| Microsoft Year 2000 Readiness Disclosure
& Resource Center |
|
|
|
| Developing Year 2000 Compliant
Applications in Microsoft Visual Basic |
|
7. Where to Take Your Dates: Storage
Issues
Most of
the work Visual Basic is called upon to perform provides a
front end to a database. The different database products all have
different capabilities and storage patterns. Not all have a native
Date or DateTime field type, and
some products that do have these field types often have ones that
are not capable of year 2000 compliance.
Working with Databases
The
field of database technology is being reshaped almost daily, as new
technologies or new versions of older technologies, emerge. Most of
your database work will be done through a proprietary API, DAO, RDO,
ADO, or ODBC. The latter three depend to some extent on an
additional driver layer that might be sourced from a third party. As
with controls, you must perform thorough acceptance testing on any
middleware that you use.
SQL issues
A major
issue when working with SQL, especially if you work outside the
United States, involves date formats in your statements. The SQL
standard is to use a United States format MM/dd/yyyy date. In other
countries, such as the United Kingdom, this format can lead to
confusion if you forget to modify the date to provide the date in
the United Kingdom format of dd/MM/yyyy. The following code shows a
function for formatting any date in a SQL statement.
Public
Function sFormatDateForSQL(ByVal idtDate As Date) As String
'
Convert the passed Date to a string in the
' US format, suitable
for using in a SQL Statement.
sFormatDateForSQL =
Format$(idtDate, "MM/dd/yyyy")
End Function
Storage patterns for legacy platforms
Wherever
possible, use the native date format provided by the database
product. Most of the latest versions of the most popular products
support dates well into the next millennium and beyond. When a date
field is not available, or is not capable of year 2000 compliance, a
little cunning is required. HereÆs a look at a couple of possible
storage patterns.
Double-precision number
By
storing your dates as double-precision numbers, you render them
immediately compatible with the Date data type of Visual Basic.
Double-precision numbers can store time as well as date information.
TimeSince
An
alternative to the above idea is to store your dates as a Long
integer containing the number of seconds, minutes, or days since a
defined base date. This base will be a date such as midnight,
January 1 1980, or midnight, January 1 2000. Conversions to and from
this format can be performed using the DateDiff
and DateAdd functions discussed earlier in this
paper. The following code shows an implementation of TimeSince. This implementation provides functions to
convert a Date to and from a Long, using a base date of January 1
2000.
Const
BASE_DATE As Date = #1/1/2000# ' Base date is 2000-01-01.
Const
INTERVAL As String = "n" ' Interval is minutes
Public
Function lDateToTimeSince(ByVal idteDate As Date) As Long
'
Convert the passed date and time to a Long integer
' containing
the minutes elapsed since the base date.
LDateToTimeSince =
DateDiff(INTERVAL, BASE_DATE, idteDate)
End
Function
Public Function dtDateFromTimeSince(ByVal
ilMinutes As Long) As Date
' Convert the passed Long integer
to a Date as
' the number of minutes since the base
date.
DtDateFromTimeSince = DateAdd(INTERVAL, ilMinutes,
BASE_DATE)
End Function
Obviously, the choice of time interval dictates the date
range available, but even if we use seconds we have a range of
approximately 135 years (slightly less than 68 years before and
after the base date). If storing time is not important or space is
at a premium, we can use days as a time interval and store our date
as a short integer.
Note This technique mimics the
storage format employed by Visual Basic itself, but has a lower
storage overhead. Visual Basic uses an 8-byte double-precision
number to store the number of seconds before or since midnight
December 30 1899. We save space by choosing a more recent base date
and a more coarse time measurement.
Both the
double-precision and TimeSince methods have the
added bonus of being numeric data, which is considerably faster for
a database to sort, search, and index than alphanumeric data.
|
