Can I fix this mm/dd/yyyy <-> dd/mm/yyyy confusion once and for all?

This seems to cause a lot of problems. In your environment, you may find that Access or SQL Server is set up for UK dates, and Windows is set up for US dates. Or vice-versa. Or some other weird combination, like SQL Server is set up for UK dates, the SQL machine's Windows is set up for US dates, but the current logged on user has a Danish locale. Combine that with an ASP page on a Windows machine in the next rack, set up for a German charset, and you can see there'll be some big trouble. Take a look at the dates formatted in this example:

<%
ReturnDateTime 1033, "English (US)"
ReturnDateTime 2057, "English (UK)"
ReturnDateTime 3081, "English (Australia)"
ReturnDateTime 1031, "German"

Sub ReturnDateTime(locale, description)
Session.LCID = locale
Response.Write "" & description & " (LCID = " & locale & ") "
Response.Write "Long date: " & FormatDateTime(Date, 1) & " "
Response.Write "Short date: " & FormatDateTime(Date, 2) & ""
End Sub
%>

This results in something like this:

English (US)
Long date: Monday, February 25, 2002
Short date: 2/25/2002

English (UK)
Long date: 25 February 2002
Short date: 25/02/2002

English (Australia)
Long date: Monday, 25 February 2002
Short date: 25/02/2002

German
Long date: Montag, 25. Februar 2002
Short date: 25.02.2002

So, to display dates to the user in the desired LCID is pretty trivial. Now, to see where the problems really come in with the short date format, try this little script (assuming SQL Server):

<%
set conn = CreateObject("ADODB.Connection")
conn.open "<connectionString>"

' create a simple table
conn.execute("CREATE TABLE dt (d DATETIME)")

' simple sub to reset locale
Sub SetUSLocale()
Session.lcid = 1033
End Sub

Call SetUSLocale()

' sub for inserting date to DB
Sub InsertDateTime(locale, description)
session.lcid = locale
sql = "INSERT dt VALUES('" & FormatDateTime(date,2) &_
" " & time & "')"
on error resume next
conn.execute(sql)
if err.number <> 0 then
response.write description & " failed. " &_
err.description & ""
err.clear
else
response.write description & " passed."
end if
End Sub

' let's insert current date / time in each locale
InsertDateTime 1033, "US English"
InsertDateTime 2057, "UK English"

' sub for displaying data from database
Sub ShowDatabaseDateTime(locale, description, dt)
response.write description & " unaltered: " & dt & " "
session.lcid = locale
response.write description & " altered: " &_
formatdatetime(d,2) & " " & formatdatetime(d,3) & ""
End Sub

set rs = conn.execute("SELECT d FROM dt")
do while not rs.eof
setUSLocale()
d = rs(0)
ShowDatabaseDateTime 1033, "US English", d
ShowDatabaseDateTime 2057, "UK English", d
rs.movenext
loop

rs.close
set rs = nothing

conn.execute("DROP TABLE dt")

conn.close
set conn = nothing
%>

Assuming your SQL Server and Windows machines are set up to accept dates in US format (mm/dd/yyyy), you should see something like the following in the results:

US English passed.
UK English failed. The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

US English unaltered: 2/25/2002 9:53:33 PM
US English altered: 2/25/2002 9:53:33 PM

UK English unaltered: 2/25/2002 9:53:33 PM
UK English altered: 25/02/2002 21:53:33

The UK English insert statement failed because SQL Server doesn't know what month 25 is. In most VB / VBA environments, such as Access, it will implicitly convert 25/02/2002 to 02/25/2002, figuring that the date was entered incorrectly. SQL Server is not so forgiving, and for good reason -- there is ambiguity implied when a database can make decisions for you. How does it know when to draw the line? Should it contemplate changing Nov. 12th to Dec. 11th or vice-versa? Of course not.

To help eliminate differences in international formatting of dates and times, it is recommended to always use YYYYMMDD hh:mm:ss format. Unfortunately, ASP/VBScript doesn't have this format built in, so we have to accommodate a bit. Here is a function I wrote to format a correct database-style date in standard ISO format, regardless of the locale:

<%
Function dbDate(dt)
dbDate = year(dt) & right("0" & month(dt), 2) &_
right("0" & day(dt),2) & " " & formatdatetime(dt,4)
End Function
%>

So, taking the above script, we can correct for differences in the LCID by passing the dates through dbDate() first (note that the only changes in the script are marked in bold):

<%
Function dbDate(dt)
dbDate = year(dt) & right("0" & month(dt), 2) &_
right("0" & day(dt),2) & " " & formatdatetime(dt,4)
End Function

set conn = CreateObject("ADODB.Connection")
conn.open "<connectionString>"

' create a simple table
conn.execute("CREATE TABLE dt (d DATETIME)")

' simple sub to reset locale
Sub SetUSLocale()
Session.lcid = 1033
End Sub

Call SetUSLocale()

' sub for inserting date to DB
Sub InsertDateTime(locale, description)
session.lcid = locale
sql = "INSERT dt VALUES('" & dbDate(now) & "')"
on error resume next
conn.execute(sql)
if err.number <> 0 then
response.write description & " failed. " &_
err.description & ""
err.clear
else
response.write description & " passed."
end if
End Sub

' let's insert current date / time in each locale
InsertDateTime 1033, "US English"
InsertDateTime 2057, "UK English"

' sub for displaying data from database
Sub ShowDatabaseDateTime(locale, description, dt)
response.write description & " unaltered: " & dt & " "
session.lcid = locale
response.write description & " altered: " &_
formatdatetime(d,2) & " " & formatdatetime(d,3) & ""
End Sub

set rs = conn.execute("SELECT d FROM dt")
do while not rs.eof
setUSLocale()
d = rs(0)
ShowDatabaseDateTime 1033, "US English", d
ShowDatabaseDateTime 2057, "UK English", d
rs.movenext
loop

rs.close
set rs = nothing

conn.execute("DROP TABLE dt")

conn.close
set conn = nothing
%>

And the results this corrected script outputs are as follows:

US English passed.

UK English passed.

US English unaltered: 2/25/2002 10:03:00 PM
US English altered: 2/25/2002 10:03:00 PM

UK English unaltered: 2/25/2002 10:03:00 PM
UK English altered: 25/02/2002 22:03:00

US English unaltered: 2/25/2002 10:03:00 PM
US English altered: 2/25/2002 10:03:00 PM

UK English unaltered: 2/25/2002 10:03:00 PM
UK English altered: 25/02/2002 22:03:00

As alternatives to session.LCID, you can try maintaining an LCID in a specific session variable and setting it on a page-by-page basis using @LCID or @CODEPAGE. If you are using Windows Server 2003 and IIS 6.0, you can experiment with the new function Response.Codepage.

For more information on date / time formatting in ASP, please see the following KB articles:

KB #218964
PRB: VBScript Date and Time Formats Change with Logged on User

KB #229690
HOWTO: Set the ASP Locale ID Per the Browser's Language Settings

KB #264063
Date/Time Functions May not be Formatted Properly in Non-English (US) Locales

KB #271587
ASP Pages Use Time/Date Format Based on User That Is Logged On

KB #306044
INFO: Behavior of Date/Time Format Differs When Accessed from ASP

An alternative is to alter the default regional settings for the server, however this may impact other sites / applications and should only be used as a last resort.

Log on to the server (either physically or through terminal services) and set the correct locale and date format
Open regedit, navigate to the following key, and export it:

HKEY_CURRENT_USER\Control Panel\International
Open the exported file in Notepad, and replace "HKEY_CURRENT_USER" with "HKEY_USERS\.DEFAULT"
Save the file, double-click it, and restart IIS

And finally, in IIS 5.1 and IIS 6.0, there is an application level property called AspLCID. You can see the documentation for this property in the IIS SDK. Also, take a look at these other MSDN Library search results.



// home // advanced search // news // categories // sql build chart // downloads // statistics
ASP FAQ

Can I fix this mm/dd/yyyy <-> dd/mm/yyyy confusion once and for all?

Related Articles