Search code examples
sql-server-2008datetimedate-formatdate-conversion

Datetime conversion error for 1 sql server, but not another

I've got 2 servers running SQL Server 2008, and I have the following query:

SELECT cast('13/1/2011' as datetime)

If I execute this query on Server A I get the result:

2011-01-13 00:00:00.000

However, if I execute this on Server B I get the result:

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

I believe this to be a UK/US date format issue as I don't get an error with 12/1/2011 but it does return 2011-12-01 00:00:00.000

How can I get Server B to get the same result as Server A? What setting needs to be changed and where?

Solution

  • It is the language setting of the login that controls how these ambiguous date formats are interpreted (though this can be overridden with an explicit SET DATEFORMAT statement).

    The DEFAULT_LANGUAGE can be changed via ALTER LOGIN