Problem with Regional settings somewhere

Question

With reference to http://stackoverflow.com/questions/280597/problem-with-date-daymonth-reversing-on-save

I have further noted that even setting the Session.LCID on the page itself is making no difference what so ever.

How could the environments be such that between test and live the asp site on live is reversing dates entered via SQL but not on test.

Both have the IUSR set to UK, both have all users set to UK, both have the SQL Account set to US English and both have Session.LCID set to 3081 (Australian English)

Why is test running " insert into datecolumn values '01/03/2008' and inserting '01/03/2008' and live is inserting '03/01/2008' "

The setups look totally identical. This must be figured out soon i'm getting quite scared that we'll never know. The problem is we may not change code or anything else. All I can do is investigate and tell them the cause. But I can't find it!

It's VB6/ASP and it's driving me do lally.

Access to the database is via a System DSN configured to use the correct SQL account.

What other info might you need.

Answer 1

In SQL Server Management Studio, in Security - Logins, right-click the user you're connecting as and click 'Properties'. The bottom combo box is marked 'Default language', change this to "British English" (not just "English").

sp_configure 'default language' sets the default language for newly created logins, and it is this that is stored in sysconfigures.

I'm assuming here that the language isn't being set in the query string?

Answer 2

I encourage you to modify the code that interacts with the database. There are 2 unambiguous date formats that you can use with SQL Server.

You can use yyyy-mm-ddThh:mi:ss.mmm or YYYYMMDD hh:mi:ss.mmm

SQL Server will never mis-interpret dates if you use one of the 2 formats listed above.

In vb, you can use the following format function to format the date in to an un-ambiguous date format that SQL Server will never mis-interpret.

Format(YourDateVariable, "yyyymmdd hh:nn:ss")