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

[si_the_geek]

First of all, thanks for sharing - it's always nice to see things that could potentially be added to the FAQs.

MICROSOFT DATES ...

While some Microsoft products use dates in the manner described, not all of them do.. while the differences tend to be minor, it is best not to call them "Microsoft dates", or say things like "How windows handles dates".

Based on my experience of various products, I think it is only safe to say that this is for Classic VB and VBA.

The OLE date format is a double-precision floating point number. ...

Indeed, as covered in the Classic VB FAQ article: Why are my dates not working properly? (or 'How should I work with dates?')

Two digit versus Four digit year...

A Date cannot have a 2 digit year, only a String can - so this only applies at the point you convert a String to a Date, which is something that is also covered in the FAQ article.

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

That is something I have not explicitly mentioned in the FAQ, and I think it is better that way - while it is possible to use mathematical code, it is easier to read/maintain "normal" date based routines like DateAdd.

There are numerous date and functions including:

I have considered creating another article listing/explaining functions like that (including examples for each), and for more some complex things too (like calculating age, etc).

However, the amount of questions that get posted about that kind of thing are far lower than for other FAQ articles I am considering - so for me this is a low priority, and will only happen after I have found enough free time to write the ones that I think are needed more.

As always, I am happy for somebody else to write it instead!

Dates As Dates

I disagree with this section for a few reasons...

To start with, it doesn't actually seem to be about Dates, but Strings containing something that represents a date - and the conversion of those Strings to Dates.

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.

Both of those statements are actually incorrect.

When a Date value is written in code, the US format is used (eg: MyDate = #8/1/2008# always means August 1st).

When a Date is displayed in VB (eg: via a Watch) or converted to a String (eg: when assigned to a Label/TextBox), it is not done in a consistent format - it is done using the "Short Date" format currently specified in Regional Settings on that computer. For example, on my computer #8/1/2008# is shown as 1/8/2008

When a String is converted to a Date, the Regional Settings should be used to interpret the String.. but there are some situations where this does not happen, and the US format is assumed instead (which is far less likely to be a problem if your computer uses the US format!). It is much safer to use your own conversion code instead (as shown near the end of the FAQ article).

Dates As Strings -- or -- Date Formats

This covers similar things to the previous section, and has basically the same flaw.

You mention using CDate, but believe it or not that has exactly the same issues as not using it - the way the String will be interpreted depends on Regional Settings (if you are lucky!).

There is also the format that Dates will be displayed in other applications, which again tends to depend on Regional Settings (for Access/SQL Server/...), but you are completely right that what you see is not the actual value.

Storing Dates

To be really picky... You mention "UDT", but I think you meant "UTC" (google only gives 142 results for "udt date", including this thread!).

While the advice is good for systems that involve multiple time zones (or potentially could in the future), in other cases it is just extra work that will give no benefit.

SQL and Dates

That is covered in more detail in these two DB FAQ articles:
How can I work with dates correctly?
How do I use values (numbers, strings, dates) in SQL statements?

[dw85745]

Si-the-Geek

As stated, purpose was for possible additional info to be added to the FAQ as I personally found it lacking. I revised this early AM. I have not incorporated your posted comments as they took place ex-post to my update. Again use as you wish.

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

[si_the_geek]

I'm not sure why you found the FAQ articles lacking - they contain the majority of what you posted, but do not contain what I consider to be debatable advice, or things which are misleading/incorrect.

While some of your changes are good, there are flaws too (eg: "Strings Which Are NOT Date Formatted" is not really apt, as they are formatted - just not in a way that the unreliable CDate can interpret).


In my opinion the only thing you posted that should be added to the FAQs is a list of Date Functions in Classic VB, which is something that I intend as a separate article at some point (but could be a while until I get around to it).

[dw85745]

Si-the-Geek

Didn't want to get into debate over this issue.
Just provide info in a different way that was more understandable -- at least to me -- maybe others.

Since you're in control of FAQ, your read is what counts -- but as one reader, FWIW, I did not get what I wanted out of the Date FAQ.

[si_the_geek]

I'm happy to change and/or add to FAQ articles if needed - and as you had trouble with these three, there does seem to be a need somewhere in at least one of them (as presumably you aren't the only one).

Unfortunately I don't understand what is missing (apart from Functions which will be added as a separate article later; and UTC/mathematical calculations, which I think are best avoided for most people) or what part(s) are explained badly.

That is part of the reason I tried to explain here the things that you have misunderstood; of course I also don't want you to accidentally write code that has bugs in it (like giving a String in US format to CDate) because I haven't explained something well enough.