I recently had a problem with moving an old ASP site to a new
server setup, and when I tried to insert anything with a date into
the DB I got the following error.
The conversion of a char data type to a datetime data type
resulted in an out-of-range datetime
Slightly annoying considering it had been working fine on a
previous server, which was Win2008 R2 and SQL Express 2008 R2
exactly the same as this current server setup. The only
difference I found is that upon setup, the SQL Express on the new
server had the default language set to us_English and I wanted
British English so I could make sure the dates were input
correctly.
After Googling around, it seems this is actually a common issue
and once the language is set… That's it! No matter what
stored procedures you run, you can't change the global language -
It will say successfully changed, but when you check its still the
US English. There is a registry hack I stumbled upon, but do you
really want to be hacking around with the registry on a live
server?
Anyway, I found the following way to get round this and its not
to change the entire SQL Server language - But change the language
for the user logging in. So for my problem, I just took the
username from the connection string (Lets say its
BobMartin) and ran this stored procedure
ALTER LOGIN BobMartin WITH DEFAULT_LANGUAGE =
British
It worked perfectly for me 