Sql-server – What date/time literal formats are LANGUAGE and DATEFORMAT safe

date formatdatetimesql serverstring-representationt-sql

It is easy to demonstrate that many date/time formats other than the following two are vulnerable to misinterpretation due to SET LANGUAGE, SET DATEFORMAT, or a login's default language:

yyyyMMdd                 -- unseparated, date only
yyyy-MM-ddThh:mm:ss.fff  -- date dash separated, date/time separated by T

Even this format, without the T, may look like a valid ISO 8601 format, but it fails in several languages:

DECLARE @d varchar(32) = '2017-03-13 23:22:21.020';

SET LANGUAGE Deutsch;
SELECT CONVERT(datetime, @d);

SET LANGUAGE Français;
SELECT CONVERT(datetime, @d);

Results:

Die Spracheneinstellung wurde auf Deutsch geändert.

Msg 242, Level 16, State 3

Bei der Konvertierung eines varchar-Datentyps in einen datetime-Datentyp liegt der Wert außerhalb des gültigen Bereichs.

Le paramètre de langue est passé à Français.

Msg 242, Level 16, State 3

La conversion d'un type de données varchar en type de données datetime a créé une valeur hors limites.

Now, these fail as if, in English, I had transposed the month and day, to formulate a date component of yyyy-dd-mm:

DECLARE @d varchar(32) = '2017-13-03 23:22:21.020';

SET LANGUAGE us_english;
SELECT CONVERT(datetime, @d);

Result:

Msg 242, Level 16, State 3

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

(This isn't Microsoft Access, which is "nice" to you and fixes the transposition for you. Also, similar errors can happen in some cases with SET DATEFORMAT ydm; – it isn't just a language thing, that's just the more common scenario where these breakages happen – and aren't always noticed because sometimes they aren't errors, it's just that August 7th became July 8th and nobody noticed.)

So, the question:

Now that I know there are a bunch of unsafe formats, are there any other formats that will be safe given any language and dateformat combination?

Best Answer

In the documentation, it is very explicitly stated that the only safe formats are the ones I demonstrated at the very beginning of the question:

yyyyMMdd                 -- unseparated, date only
yyyy-MM-ddThh:mm:ss.fff  -- date dash separated, date/time separated by T

However, it was recently brought to my attention that there is a third format that is equally immune to any language or dateformat settings:

yyyyMMdd hh:mm:ss.fff    -- unseparated date, no T separator

TL;DR: This is true. For datetime and smalldatetime.

Read on for the longer version, and about as much proof as you're going to get.

There is a loophole that explains this - while the main text body fails to acknowledge yyyyMMdd hh:... as a format safe from transposed language or date format interpretations, there is a little blurb that says the date portion of such a string is not validated depending on dateformat settings:

enter image description here

It is rather unlike me to just take the documentation at its word, usually. You can say I'm a bit skeptical. And the language is ambiguous here, too - it just states that this is about the combination of date and time, not calling out the space explicitly (that could be a carriage return, for all I know). It also says that it isn't multi-language, which means it could fail in certain languages, but we'll find out shortly that that is incorrect as well.

So I set out to prove that no combination of language/dateformat could make this specific format fail.

First, I created a little block of dynamic SQL for each language:

EXEC sys.sp_executesql @sql, N'@lang sysname', N'us_english';

This produced 34 rows of output like this:

EXEC sys.sp_executesql @sql, N'@lang sysname', N'us_english';
EXEC sys.sp_executesql @sql, N'@lang sysname', N'Deutsch';
EXEC sys.sp_executesql @sql, N'@lang sysname', N'Français';
EXEC sys.sp_executesql @sql, N'@lang sysname', N'日本語';
...
EXEC sys.sp_executesql @sql, N'@lang sysname', N'简体中文';
EXEC sys.sp_executesql @sql, N'@lang sysname', N'Arabic';
EXEC sys.sp_executesql @sql, N'@lang sysname', N'ไทย';
EXEC sys.sp_executesql @sql, N'@lang sysname', N'norsk (bokmål)';

I copied that output to a new query window, and above it, I generated this code, which would hopefully try to convert that same date (the 13th of March) to the 3rd day of the 13th month in at least one case:

DECLARE @sql nvarchar(max) = N'
SET LANGUAGE @lang;
SET DATEFORMAT ydm;
SELECT @@LANGUAGE, CONVERT(datetime, ''20170313 23:22:21.020'');';

Nope, every language worked just find in ydm. I tried every other format, too, and also every date/time data type. 34 successful conversions to March 13th, every time.

So, I do concede to @AndriyM and @ErikE that, indeed, there is a 3rd safe format. I will keep this in mind for future posts, but I've pounded the drums about the other two in so many places, I'm not going to hunt them all down and correct them now.

By extension, you would think this one would be safe, but no:

yyyyMMddThh:mm:ss.fff    -- unseparated date, T separator

I think in every language, this will yield the equivalent of:

Msg 241, Level 16, State 1, Line 8
Conversion failed when converting date and/or time from character string.

For completeness, there is a fourth safe format, but it is only safe for conversions to the newer date/time types (date, datetime2, datetimeoffset). In these cases the language settings can't interfere:

yyyy-MM-dd hh:mm:...

However, I highly recommend against its use because it only works for the newer types, and the old ones are still in a massive amount of use, in my experience. Why have the dashes there when everywhere else (or in fact in that same code, if the data type changes) you have to remove them?

SET LANGUAGE Deutsch;
DECLARE @dashes char(10) = '2017-03-07 03:34';
DECLARE @d date = @dashes, @dt datetime = @dashes, @dt2 datetime2 = @dashes;

SELECT DATENAME(MONTH,@d), DATENAME(MONTH,@dt), DATENAME(MONTH,@dt2);

Even given the same source string, the conversions yielded quite different results:

März    Juli    März

The format that works for datetime (yyyyMMdd) will also always work for date and the other new types. So, IMHO, just always use that. And given the third format for types with date/time (yyyyMMdd hh:...), this will actually allow you to be more consistent - even if the date component is always a little bit less readable.

Now it will just take me a few years, give or take, to get into the habit of demonstrating the three safe formats when I'm talking about string representation of dates.

Related Question