SQL Server Language Settings

Just had a query from a mate about dates in SQL Server.

If you're reading this in the States, or anywhere that uses mm/dd/yyyy date formats, don't bother reading on. If, however, you're in a dd/mm/yyyy region then you may well find this helpful.

I was working on an application recently that worked just fine until I made a copy of the database on the same SQL server. The new copy wouldn't work and I couldn't see what had changed. Where I was passing dates from ASP to SQL in a string format e.g.

exec sp_analysis '04/30/2004'

it was falling over with the dreaded "Syntax error converting datetime from character string.". The date you see there is in mm/dd/yyyy format because I use a nifty little function to switch it round before concatenating it into the string. I fixed the problem by changing that function to return the date exactly as it was passed in, without doing the switch.

But that still didn't answer the question of Why Did It Do That? When I finally realised, I slapped my forehead with my palm and cried "Of course!" and "You donkey!". When you create a Login on SQL Server (before you assign them permissions for any databases) there is a Language setting. Previously, this has always defaulted to English which, as we all know, is American English. When I copied the database, I created the user from scratch and assigned them British English...as you would. This caused it to treat all dates as dd/mm/yyyy instead of mm/dd/yyyy, which the application had been used to!

Anyway, you can do it through Enterprise Manager, under Security > Logins or you can do it using script (of course!), instructions here:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_da-di_2tk5.asp