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?

Vivien

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
• All of the links in Dating Responsibly

Serenata

See my answer here how to do it within a user defined function. – Acosta

@Eli I think you're missing the point I was trying to make. If one person is in England and passes a string '09/04/2014' and another person is in the USA and passes '09/04/2014', how does your function magically know which one of them meant April 9 and which one meant September 4? – Serenata

I must admit that I didn't read through your whole thread, I was looking for a month translation solution and while I was searching for it I came across your answer, so I thought some people may find it helpful... – Acosta

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.

Denudation

@AaronBertrand Correct. Are there any infallible approaches to handle this on SQL? I mean, receiving a string representing a date on any format and make the conversion safely, without any nasty logic? – Denudation

No, there isn't. You need to control the input string. If a user passes 05/06/2012, because they were allowed to type it in any format they wanted, how can SQL Server reliably determine whether the user meant May 6 or June 5? – Serenata

Answer 3

You can also change de default date format for each logins and/or for each future added logins (without the needs to do "SET DATEFORMAT" in each session.

You go in SQL Management Studio / Security / Logins. Right-clic the login, then Properties. You will see "Default Language" at the bottom.

If you want to make sure any logins added in the future get the "good" language. You right-click on your server root, then Properties and Advanced page. There is an option "Default Language" there too that is used to newly created logins.

Pent

you shouldn't just comment "thanks" here.... but I need to, so: thanks. – Levasseur