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


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

  • The problem here seems to be that the default language is British but it the database is still using yyyy-dd-mm for some reason. Feb 13 '12 at 14:22
  • no, your logins have a different default language. What does the quote in my answer say? Have you read it?
    – gbn
    Feb 13 '12 at 14:25
  • I have gbn... Sorry I wasn't clear in my comment. I have checked the logins default language and it is also British English. Feb 13 '12 at 14:28
  • 1
    @AlexAndronov - In any case, you should always specify the date style on your convert, to avoid this problem. In your case, you should do: SELECT CONVERT(DATETIME,'2012-02-13',120)
    – Lamak
    Feb 13 '12 at 14:28
  • 3
    @AlexAndronov - British English is not the correct language for this format. Check what the login in your old server was using. For me it fails under "British English" but succeeds under "English" or "Simplified Chinese". My guess is that your old login was using US English. Feb 13 '12 at 14:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.