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."