2

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.

Improve this question
0

1 Answer 1

Reset to default
8

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

Improve this answer
6
  • 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
    Commented Feb 13, 2012 at 14:22
  • no, your logins have a different default language. What does the quote in my answer say? Have you read it?
    – gbn
    Commented Feb 13, 2012 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
    Commented Feb 13, 2012 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
    Commented Feb 13, 2012 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
    Commented Feb 13, 2012 at 14:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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