Databases - International Date Formats
January 7th, 2008 | by programming |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() & “
”
‘English - Australia LCID
Session.LCID = 3081
Response.Write Date() & “
”
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.
Now that we have a firm understanding of how Dates are stored in the database, let’s think back to the String that we passed to the Query Engine. Keep in mind that we didn’t pass the Query Engine a date - we passed it a String. When the Query Engine works through our SQL statement String and gets to:
fldDateOrdered = #10/04/2001#
It says: “OK we are going to put #10/04/2001# into the fldDateOrdered field and because it is surrounded by date delimiters (#) I must do an explicit conversion to the underlying value.” This underlying value (36991) is what will actually be stored in the database. This is where the Query Engine works on the fact that Dates are based on the American premise of mm/dd/yyyy, there is no way of getting around this.
The common misconception is that you can change the way the database Query Engine expects the date format by changing the format of the field in the table to display the dates as Medium, or Long Formatted Dates. However, regardless of the formatting option that you choose for the field, the underlying number stored in the database will remain the same and the Query Engine still expects the date in the American premise of mm/dd/yyyy. Therefore if you generally think of Dates in the format of dd-mm-yyyy (the non-American standard) then you will be in for a confusing time when converting Dates between your ASP page’s Locale ID and the Database Query Engine.
Now that I’ve explained the problem, I’ll show you how to fix it. You need to convert your Date to a value that is not ambiguous. We’ve already seen that 6/4/2001 can be mistaken by people to mean different Dates (although it is not mistaken by the Database). Therefore there are only two viable ways of producing a Date String which can be read in any LCID and* the database from the one format. The first way is to use the VBScript FormatDateTime function and select vbLongDate as the formatting option, which will produce a Date String that looks like this: Tuesday, April 10, 2001. Unfortunately this format cannot be converted by the Query Engine.
The alternative format for a Date which can be read the same way in any LCID and can be interpreted by the Query Engine is dd-MMM-yyyy (where MMM is the three-letter abbreviation of the month). Because there is no VBScript function to convert a Date into this format we would need to write our own:
Function MediumDate (str)
Dim aDay
Dim aMonth
Dim aYear
aDay = Day(str)
aMonth = Monthname(Month(str),True)
aYear = Year(str)
MediumDate = aDay & “-” & aMonth & “-” & aYear
End Function
Here’s an example of the MediumDate function in action, showing how to use it to get rid of your Date problems:
strSQLUpdate = “UPDATE tblOrders ” & _
“SET fldDateOrdered = #” & MediumDate( Date() ) & “# ” & _
“WHERE fldStatus = open ; “
Regardless of your LCID or your Server’s settings the following string will be sent to the Query Engine:
“UPDATE tblOrders SET fldDateOrdered = #10-Apr-2001# WHERE fldStatus = open ;”
The secret to why this works is that the Database understands how to read the dd-MMM-yyyy format, and, as you can plainly see, 6-Jun-2001 will always be the 6th day of June 2001, no matter how you unpack it.