Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 7 down vote accepted

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

share|improve this answer
    
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. –  Alex Andronov 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. –  Alex Andronov 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. –  Martin Smith Feb 13 '12 at 14:28

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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