[RESOLVED] For Si_the_Geek (Date Discussion)

[dw85745]

You might wish to review this to see if you'd like to include in FAQ. Hope it is of some benefit. Some of the formatting was lost in posting, even with quotes, but nothing I can correct.

David

MICROSOFT DATES
as of 7/30/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 functions including:

DateAdd Date Time
Day Month TimeSerial
DateDiff Now TimeValue
DatePart Year
DateSerial
DateValue

Dates As Dates

Dates are distinguished from strings in that date string formats must contain valid date delimiters or a text string that identifies it as date, such as the name of the month (Oct or October). Valid date delimiters (ignore quotes) are:

“/ “ as in mm/dd/yyyy
“-“ as in mm-dd-yyyy
“:” as in hh:mm:ss
“#” as in #10/26/1950#; #Oct 26, 1950#


Microsoft’s uses the standard US date string format of (mm/dd/yyyy hh:mm:ss) to represent the double precision number to the user. Microsoft ASSUMES this order when calculating dates. If another order is used ( such as the European format -- dd/mm/yyyy), the date must be converted to the US format in order to use the Date/Time functions and obtain the correct result.

Whether a service pack exists which allows dates to be calculated in another date format is unknown.

Dates As Strings -- or -- Date Formats

There is no such thing as a string date. There are however, strings which are formatted to be recognizable as a date. Strings formatted as dates probably cause programmers more problems than any other issue, since they appear to be a date, but in fact are just a string of characters.

Strings MUST be converted to a date (normally with CDate) to do date arithmetic or use the date functions. If the string is in US date string format, or contains Valid date delimiters, such as --
YYYY/MM/DD HH:MM:SS or YYYY-MM-DD HH:MM:SS -- CDate takes care of this conversion.

However 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.

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.

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 UDT date can then
be adjusted to any time zone when presented to the user.

Using UDT is especially useful if dates are transmitted across time zones. The use of UDT 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 UDT 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’s implementation varies so depending on the programming supplying the SQL, date delimiters may be different.