MICROSOFT DATES
as of 7/31/2008
Where Windows handles dates
One article mentioned that 4 different DLL’s deal with dates. To date, I have identified one:
OLEAUT32.dll
How windows handles dates
The OLE date format is a double-precision floating point number. This double precision number (date) uses the whole number portion to store the date and the decimal portion to store time. Since the date is a double, values can be added and subtracted to obtain another date and/or time.
OLE uses 30 December 1899 00:00:00 as its base date. Thus, a value of 0.0 corresponds to 30 December 1899 00:00:00. However, this base date can only be used when using a four digit year. Two digit years are handled differently, and present a number of issues.
Two digit versus Four digit year
It is recommended that all two digit years be converted to four digit years, otherwise Microsoft’s date functions may return an incorrect result.
Because of the change from the 20th to the 21st century (Year 2000 or Millenium), Microsoft did some unique programming to deal with two digits dates. Various schemes were used in different Microsoft applications, including a sliding date scale. See
http://www.fmsinc.com/tpapers/year2000/index.html for a more through explanation.
Functions to deals with dates
Since the date is a double, like any number it can be added or subtracted. Consequently, Microsoft’s provided date routines do NOT have to be used. If two digits year dates are used with these date/time functions, they may return an incorrect result. So programmer beware. If a four digit year is used, no problem should arise. There are numerous date and time functions including:
Get or Set System Date or Time
Now, Date, Time
Date Functions
Day, Month, Year
DateAdd, DateDiff, DatePart, DateSerial , DateValue,
Time Functions
Hour, Minute, Second
TimeSerial, TimeValue
Date/Time Verification
IsDate
String Formatted Dates
As previously stated a date is a double precision number – period!.
Strings are formatted as dates so they are recognizable to the user.
The purpose of a string formatted date is to allow easy conversion between the string formatted date and
the date itself (double precision number) in order to do date arithmetic or use the date/time functions. The CDate function is used to make this conversion.
In order to be considered a valid string formatted date, the string must contain valid date delimiters or text that identify it as date, such as the name of the month (Oct or October). Valid date delimiters (ignore quotes) which allow the use of CDate are:
“/ “ as in mm/dd/yyyy
“-“ as in mm-dd-yyyy
“:” as in hh:mm:ss
“#” as in #10/26/1950#; #Oct 26, 1950#
If these delimiters are used, CDate will attempt to convert the string to the actual double precision number called Date. String date formats such as -- YYYY/MM/DD HH:MM:SS or YYYY-MM-DD HH:MM:SS – are easily converted by CDate.
Microsoft assumes string formatted dates are in the standard US string date format of (mm/dd/yyyy hh:mm:ss AM/PM). If another string date representation is used ( such as the European string format -- dd/mm/yyyy), it is recommended that the string be converted to the US string format before use, otherwise the Date/Time functions may give an incorrect result. Test your string date format using CDate before accepting that the converted date is in fact a valid date. Whether a service pack exists which allows dates to be calculated in another date format is unknown.
Some application, for example Access Date type, have a default display date format of mm/dd/yyyy hh:mm:ss AM/PM. This is a string. The actual date is the double precision number. Access automatically makes the CDate conversion when referencing a field defined as a Date type.
Strings Which Are NOT Date Formatted
Strings such as -- YYYYMMDD or YYYYMMDDHHMMSS -- can NOT be converted to a date using CDate, as they do not contain any valid date delimiters. Consequently, the string must be broken into its separate parts using the String functions (e.g Left$, Right$, Mid$, etc.) and then reassembled into a valid string date format before conversion can occur.
Storing Dates
The best way to store a date (especially in a database) is to convert the date/time to Universal Time. This way ALL stored dates will have a consistent time zone base date/time. The UTC date can then
be adjusted to any time zone when presented to the user.
Using UTC is especially useful if dates are transmitted across time zones. The use of UTC eliminates the any confusion as to what was the time zone that this date / time represents.
I have seen several articles discussing the differences between UTC and GMT. The differences are beyond the scope of this article.
SQL and Dates
Just like Microsoft date functions, SQL needs a number to do date calculations. SQL implementation varies so depending on the vendor supplying the SQL, date delimiters may be different
