||There are many parts to the answer to this question.|
To begin with, we need to understand just how dates and times are stored in various parts of a system. Let's start with VBScript and Access, which happily (or maybe not, depending on your viewpoint) share the same storage format. This is because both rely upon the COM structure named
VARIANT to hold a date/time, specifically, the
vtDate variation of the VARIANT.
In short, a COM vtDate is stored as a double precision floating point number. Yes, the same kind of number that you might get by doing
circumference = 3.14159265 * radius
The fact that the variant is known to be a vtDate is the only thing that distinguishes it from any other number. And the meaning of the number is:
-- The integer portion (the part to the left of the decimal point) represents the number of days that have elapsed since 30 December 1899. A negative number indicates a date before 30 December 1899.
-- The fractional portion (the part to the right of the decimal point) represents the time-of-day as a fraction of 24 hours.
So the number 32.75 is the same as 1/31/1900 6:00:00 PM or, if you use 24-hour time and European-style dates, 31/1/1900 18:00:00.
Hopefully, you noticed something just then: I said that 32.75 represented either of those two formatted times. Actually, it represents ANY equivalent date and time. The point is, the internal representation of a date and time has nothing to do with how it is displayed to the user! And this is true whether you are displaying the date/time via Access, via VBScript, via JScript, or via Excel (yes, it uses the same internal format).
This is a very very important point, so grasp it thoroughly: The display of a date/time (and, indeed, of any number!) is completely independent of the way it is stored.
Not only that, but the display is not carried from one application to another! So the fact that you decided to ask Access to display your date/time values as "mm/dd/yyyy hh:mm PM" has no effect on how that same data will be displayed by VBScript! You must independently specify the display format in each place a date/time (and, again, any number) is displayed.
To demonstrate some of the above, consider these VBScript samples (go ahead and try them on your own machine!):
dateA = DateSerial( 2002, 3, 5 )
dateB = CDate("5/3/2002")
dateC = Date()
dateD = CDate( 32.0 )
Response.Write "dateA is " & dateA & "<br>"
Response.Write "dateB is " & dateB & "<br>"
Response.Write "dateC is " & dateC & "<br>"
Response.Write "dateD is " & dateD & "<br>"
All of those first four statements correctly initialize a Date/Time value in VBScript. ("Did he say date/Time? I don't see any time." Just wait.)
But now comes some tricky parts: (1) Did you see the same date for
dateA as you did for
dateB? No? Then you are probably living in the USA. In most of the rest of the world, those two are the same. (2) Did you really try that code? What did you think of the display of
At this point, you might be asking yourself "But if a date/time value always has a time, as you are claiming, how come we don't see it displayed?" Quite simply, because that is the default choice of VBScript when the time is midnight!
So, time for some more examples:
dateE = TimeSerial( 15, 20, 25 )
dateF = CDate("15:20:25")
dateG = CDate("3:20:25 PM")
dateH = CDate(0.639178240740741)
Response.Write "dateE is " & dateE & "<br>"
Response.Write "dateF is " & dateF & "<br>"
Response.Write "dateG is " & dateG & "<br>"
Response.Write "dateH is " & dateH & "<br>"
Convinced, yet? And one more short demo:
dateTimeA = dateA + dateE
Response.Write "dateTimeA is " & dateTimeA & "<BR>"
I invite you to add any of those date-only values to any of the time-only values and inspect the results.
What about other databases and other components?
Do they follow these same rule? Short answer: No.
The longer answer is that when a date/time value is retrieved via ODBC or OLE DB from a database, it is automatically converted from whatever the internal for used by that database is to the COM vtDate variant noted above. Once again, this means that no matter how the date/time was formatted in the database product, itself, that format is completely lost when the value only is retrieved into ADO and VBScript.
The exact format used by other databases is highly variable. You can read about how SQL Server stores date/time values here:
Look down on that page to the "Remarks" section to learn the actual form used.
For another example: MySQL actually does use a string, of sorts. YYYYMMDDHHMMSS is the canonical form used, though you can specify other variations if you understand the details. Beyond this, I leave you to find the internal form in the docs for the DB you are using.
But the important points, once again, are these:
(1) The internal format used by the database doesn't really matter, since it will be converted to ADO/VBScript/COM internal format automatically.
(2) Any display characteristics given to the field by the database for display purposes are completely lost when the value is transferred to VBScript.
We noted, above, that if the time-of-day is exactly midnight (that is, if the fractional portion of the number that represents the date is zero), then by default VBScript does not display the time. Similarly, if the date is 30 December 1899 (that is, if the integer portion of the number that represents the date is zero), then by default VBScript does not display the date. These are the only ways that "control" the default VBScript display of a date/time!
Just remember, though, that you can override the default VBScript display using the FormatDateTime function. And, of course, if you write
HOUR(someDateTime) you will get a valid zero value if the time is midnight.
So don't be afraid to experiment with dates and times! But don't expect any formats you set one place to carry over to any other place.