When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
ASPFAQs.com
Message Board
Related Web Technologies
User Tips!
Coding Tips

Sections:
Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Security
Stump the SQL Guru!
XML Info
Information:
Feedback
Author an Article
ASP ASP.NET ASP FAQs Message Board Feedback

The 4 Guys Present: ASPFAQs.com

Jump to a FAQ
Enter FAQ #:
..or see our 10 Most Viewed FAQs.

4GuysFromRolla.com : ASP FAQS : Dates and Times


Question:

How are dates and times stored in the various components of ASP? Why does the appearance of a date and/or time change depending on what I use to display it?


[Print this FAQ]

Answer: 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 dateD? Hmmm??

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:
http://msdn.microsoft.com/library/en-us/tsqlref/ts_da-db_9xut.asp
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.



And some last tidbits:


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.


FAQ posted by Bill Wilkinson at 3/22/2002 4:35:41 PM to the Dates and Times category. This FAQ has been viewed 75,392 times.

Do you have a FAQ you'd like to suggest? Suggestions? Comments? If so, send it in! Also, if you'd like to be a FAQ Admin (creating/editing FAQs), let me know! If you are looking for other FAQs, be sure to check out the 4Guys FAQ and Commonly Asked Messageboard Questions!

Most Viewed FAQs:

1.) How can I format numbers and date/times using ASP.NET? For example, I want to format a number as a currency. (761643 views)
2.) I am using Access and getting a 80004005 error (or a [Microsoft][ODBC Microsoft Access Driver] The Microsoft Jet database engine cannot open the file '(unknown)' error) when trying to open a connection! How can I fix this problem? (207777 views)
3.) How can I convert a Recordset into an array? Also, how can I convert an array into a Recordset? (202549 views)
4.) How can I quickly sort a VBScript array? (196039 views)
5.) How can I find out if a record already exists in a database? If it doesn't, I want to add it. (156019 views)
6.) How do I display data on a web page using arrays instead of Do...While...MoveNext...???... (152331 views)
7.) When I get a list of all files in a directory via the FileSystemObject, they aren't ordered in any reasonable way. How can I sort the files by name? Or by size? Or by date created? Or... (140381 views)
8.) For session variables to work, must the Web visitor have cookies enabled? (110162 views)
9.) Can I send emails without using CDONTS? (107083 views)
10.) How can I take the result of a SELECT...MULTIPLE or a group of same-named checkboxes and turn it into a query? That is, if the user selects 3 answers, how can I construct a query that looks for all 3? (106308 views)
Last computed at 9/17/2007 3:22:00 AM


ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article