YYYY-MM-DD has changed to YYYY-DD-MM after moving SQL Server to new domain how do I switch back? I have moved the domain of my SQL Server and while everything is mainly working the date format for queries seems to have changed.

So if I run this:

select convert(datetime,'2012-02-13')


I get


  Msg 242, Level 16, State 3, Line 1
  The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.


When I run

select @@Language, @@Langid


I get


  British | 23


I have tried to set sp_defaultlanguage but this seems to make no difference.

I have also checked the regional settings in control panel and that seems to also be set to British.

Even this code sample causes a problem:

set language british

select convert(datetime, '2012-02-13')


With the result


  Changed language setting to British.
  Msg 242, Level 16, State 3, Line 3 The conversion of a varchar data type to a datetime data type
  resulted in an out-of-range value.
The Windows setting do not affect SQL Server parsing dates. There is a server level default language that can be overridden by one set at the login level.

As it stands, the "safe" format for SQL Server is yyyymmdd anyway. Try to use that

From CREATE LOGIN


  Specifies the default language to be assigned to the login. If this option is not included, the default language is set to the current default language of the server. If the default language of the server is later changed, the default language of the login remains unchanged. 


For the server, you'd use "default language" via sp_configure

And you can run SET LANGUAGE or SET DATEFORMAT anytime you want of course