How does SQL Server decide format for implicit datetime conversion?

Question

declare @str_datetime varchar(50) set @str_datetime='30-04-2012 19:01:45' -- 30th April 2012 declare @dt_datetime datetime select @dt_datetime=@str_datetime 

This is giving following error:

Msg 242, Level 16, State 3, Line 4
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

My question is how SQL Server decides which format to use for implicit datetime conversion?

Answer 1

This can depend on a variety of factors - the operating system's regional settings, the current user's language and dateformat settings. By default, Windows uses US English, and the user's settings are US English and MDY.

But here are some examples to show how this can change.

User is using BRITISH language settings:

-- works: SET LANGUAGE BRITISH; SELECT CONVERT(DATETIME, '30-04-2012 19:01:45');  -- fails: SELECT CONVERT(DATETIME, '04/13/2012'); GO 

(Error)

Msg 242, Level 16, State 3, Line 5
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

User is using Français:

-- works: SET LANGUAGE FRENCH; SELECT CONVERT(DATETIME, '30-04-2012 19:01:45');  -- fails: SELECT CONVERT(DATETIME, '04/13/2012'); GO 

(Error)

Msg 242, Level 16, State 3, Line 1
La conversion d'un type de données varchar en type de données datetime a créé une valeur hors limites.

User is again using Français:

SET LANGUAGE FRENCH;  -- fails (proving that, contrary to popular belief, YYYY-MM-DD is not always safe): SELECT CONVERT(DATETIME, '2012-04-30'); GO 

(Error)

Msg 242, Level 16, State 3, Line 1
La conversion d'un type de données varchar en type de données datetime a créé une valeur hors limites.

User is using DMY instead of MDY:

SET LANGUAGE ENGLISH; SET DATEFORMAT DMY;  -- works: SELECT CONVERT(DATETIME, '30-04-2012 19:01:45');  -- fails: SELECT CONVERT(DATETIME, '04-30-2012'); GO 

(Error)

Msg 242, Level 16, State 3, Line 2
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Your best bet, always, is to use ISO standard, non-regional, safe, unambiguous date formats. The two I typically recommend are:

YYYYMMDD                  - for date only. YYYY-MM-DDTHH:MM:SS[.mmm] - for date + time, and yes that T is important. 

None of these fail:

SET DATEFORMAT MDY; SET LANGUAGE ENGLISH; SELECT CONVERT(DATETIME, '20120430'); SELECT CONVERT(DATETIME, '2012-04-30T19:01:45'); SET LANGUAGE FRENCH; SELECT CONVERT(DATETIME, '20120430'); SELECT CONVERT(DATETIME, '2012-04-30T19:01:45'); SET LANGUAGE BRITISH; SELECT CONVERT(DATETIME, '20120430'); SELECT CONVERT(DATETIME, '2012-04-30T19:01:45'); SET DATEFORMAT DMY; SELECT CONVERT(DATETIME, '20120430'); SELECT CONVERT(DATETIME, '2012-04-30T19:01:45'); 

Therefore, I strongly recommend that instead of letting users type in free text date formats (or that you use unreliable formats yourself), control your input strings and make sure they adhere to one of these safe formats. Then it won't matter what settings the user has or what the underlying regional settings are, your dates will always be interpreted as the dates they were intended to be. If you are currently letting users enter dates into a text field on a form, stop doing that and implement a calendar control or at least a pick list so you can ultimately control the string format that is passed back to SQL Server.

For some background, please read Tibor Karaszi's "The ultimate guide to the datetime datatypes" and my post "Bad Habits to Kick : Mis-handling date / range queries."

Answer 2

By default, the date format for SQL server is in U.S. date format MM/DD/YY, unless a localized version of SQL Server has been installed. This setting is fine for cases when an application that requires this functionality is deployed in a manner guaranteeing that dates are used and inserted in the same format across all platforms and locations where the application is used.

However, in some cases the date must be in a DD/MM/YY format because many countries/regions use this format rather than the U.S. default of MM/DD/YY. This is especially an issue for international applications that are distributed all over the world.

Source

So you what you need to do is set date format before hand as so:

SET DATEFORMAT dmy GO 

And then your query will work.