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:
This results in something like this:
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):
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:
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:
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):
And the results this corrected script outputs are as follows:
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:
PRB: VBScript Date and Time Formats Change with Logged on User
HOWTO: Set the ASP Locale ID Per the Browser's Language Settings
Date/Time Functions May not be Formatted Properly in Non-English (US) Locales
ASP Pages Use Time/Date Format Based on User That Is Logged On
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.
Related ArticlesCould I get some help with JOINs?
How can I tell which version of MDAC I'm running?
How do I access MIN, MAX, SUM, COUNT values from SQL statements?
How do I change column order in a table structure?
How do I change the order of columns in a table?
How do I concatenate strings from a column into a single row?
How do I convert columns of values into a single list?
How do I determine if a database exists?
How do I document / compare my SQL Server database(s)?
How do I get the IDENTITY / AUTONUMBER value for the row I inserted?
How do I solve 'ADO Could Not Find The Specified Provider'?
Should I use BETWEEN in my database queries?
Why can't I use the * wildcard in a database search?
Why do I get 'Syntax Error in INSERT INTO Statement' with Access?
Why do I get weird results when using both AND and OR in a query?
Why do some SQL strings have an 'N' prefix?
Why does AbsolutePosition return as -1?
Why doesn't SQL Server allow me to separate DATE and TIME?
Why is Query Analyzer only returning 255 characters?
Why should I avoid NULLs in my database?
How do I deal with an apostrophe (') in a SQL statement?