Published: Tuesday, April 10, 2001
Working with Databases and International Date Formats
By Darren Neimke
| Workaround for Non-English Countries |
|---|
|
Darren's article works wonders for international, English-speaking countries, like England, the
United States, Australia, and so on. However, in non-English speaking countries, the months of the
year are spelled differently and, hence, have different abbreviations. If you are from a non-English
speaking country you may wish to read Giuliano Sauro's article:
Using the ISO Date Format for International Dates.
For general answers to your date/time questions, be sure to check out the
Date and Times FAQ Category on
ASPFAQs.com!
|
If you've ever written a script that needs to UPDATE or INSERT Date
values in a database and you live outside of North America, then I'd nearly bet that
(like me) you've stayed up until midnight cursing the fact that all dates in SQL Server and
MS-Access are natively converted from US date format (mm/dd/yyyy).
Why is that a problem I hear you ask? Simple, I'll tell you the answer to that question on
6/4/2001. Did you see that? No? OK then, what is 6/4/2001? Well if you are converting dates
using mm/dd/yyyy format then the answer would be the 4th-Jun-2001. On the other
hand if you were converting dates using dd/mm/yyyy format (as is the case in
Australia and the UK) then the answer would be 6th-Apr-2001. To see what I mean, if we copy
this code into an ASP page and run it:
'English - United States LCID
Session.LCID = 1033
Response.Write Date() & "<br />"
'English - Australia LCID
Session.LCID = 3081
Response.Write Date() & "<br />"
|
the results are:
4/10/2001
10/04/2001
For more information on using Session.LCID to alter locale-specific information,
be sure to read: Using the Locale Identifier (LCID).
You can view a list of all of the valid LCIDs here.
Now, before I explain how to remedy the problem, which, incidentally I already have
anyway ;), first let's try to get a better understanding of what exactly is
happening. Let's say that we produce an SQL String which looks strikingly like this:
Dim strSQLUpdate
' If you're using SQL Server...
strSQLUpdate = "UPDATE tblOrders SET fldDateOrdered = '" & Date() & "' " & _
"WHERE fldStatus = open ;"
' If you're using MS-Access...
strSQLUpdate = "UPDATE tblOrders SET fldDateOrdered = #" & Date() & "# " & _
"WHERE fldStatus = open ;"
|
The VBScripting engine interprets the Date() function as a value, formats it
based on your Server/LCID settings, and then packages it all up as a *String* to send off to
the database's Query Engine. Once the Query Engine recieves this SQL statement it goes about
the job of Analyzing and Optimizing the query before passing it on the the Storage engine to
actually file the information away in the database.
To see why this is a problem we simply need to understand that all dates are stored in
the database as numbers. In order to be able to store a date as a number, the date has to
be converted to something other than the standard calendar format. The numeric representation
of dates is called a Julian, or Serial, date. To do this, the date is converted to an offset
from a fixed point in time.
In the case of Microsoft Access, this offset is 30th-Dec-1899, and all dates are stored as
the number of days since this date. Thus 7/7/93 is stored as 34157,
meaning 34,157 days since 30th-Dec-1899. Negative numbers represent dates prior to
30th-Dec-1899.
Since adding 1 to a date represents 1 day or 24 hours, each hour is stored as .041666...,
or 1/24 of a day. In Microsoft Access all times are stored as a fraction of a day. Each hour
is 1/24 of a day, each minute 1/1440, each second 1/86400. So 3:00 is stored as .125
(or 1/8 of a day), and 16:00 is stored as 0.666..., (or 2/3 of a day). Conversely,
0.2 represents 4:48 hours (1/5 of a day), and so on.
Therefore we see that the following snippet produces a result of 28/02/1900 6:00:00 AM
(assuming your LCID is set to display dates in dd-mm-yyyy format):
Response.Write CDate( CDbl(60.25) )
|
This is because 28th-Feb-1900 is 60 days added to 30-Dec-1899, and 06:00 AM is obviously a
quarter (or .25) of the way through the day itself.
The general point here is that the underlying value stored in the database is simply
a DOUBLE.
Now that we know how a database physically stores a date (answer: as a DOUBLE),
we're ready for Part 2, in which we'll look at how
the Query Engine converts the SQL string containing the date (which is a string itself) into
the proper format.
Read Part 2!
