Sql-server – YYYY-MM-DD has changed to YYYY-DD-MM after moving SQL Server to new domain how to switch back

date formatsql-server-2008

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.

Best Answer

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

Related Solutions

Sql-server – Statement could not be prepared

There are a couple JOIN combinations that seem abnormal to me.

For example:

left outer join dbo.vwQuoteStatus 
     inner join appdb.ABC_LABS_MSCRM.dbo.QuoteExtensionBase qeb 
             on dbo.vwQuoteStatus.AttributeValue = qeb.ABC_QuoteStatus

vs.

left outer join dbo.vwQuoteStatus 
                appdb.ABC_LABS_MSCRM.dbo.QuoteExtensionBase qeb 
             on dbo.vwQuoteStatus.AttributeValue = qeb.ABC_QuoteStatus

SQL Server – Different Date Handling on Same Server

If I understand the problem correctly, when both users have British as default language, one user's request goes through fine because the language setting changes automatically to us_english. But for another user, the language setting doesn't change automatically, hence the error.

The BOL states following regarding default language and how it can be overridden.

The default language for a login can be overridden by using CREATE LOGIN or ALTER LOGIN. The default language for a session is the language for that session's login, unless overridden on a per-session basis by using the Open Database Connectivity (ODBC) or OLE DB APIs. Note that you can only set the default language option to a language ID defined in sys.syslanguages (0-32). When you are using contained databases, a default language can be set for a database by using CREATE DATABASE or ALTER DATABASE, and for contained database users by using CREATE USER or ALTER USER. Setting default languages in a contained database accepts langid value, the language name, or a language alias as listed in sys.syslanguages.

Based on this the following can affect the language setting.

Database default
User default
DSN setting, connection strings, etc..
SET LANGUAGE in session

If underlying statements can't be changed, is there a setting that can affect it in the third party app? Or is there are trigger for a certain user login? Though I believe it's one or a combination of 2 or more in the above list....

Best Answer

Related Solutions

Sql-server – Statement could not be prepared

SQL Server – Different Date Handling on Same Server

Related Question