How SQL Server handles the date format YYYY-MM-DD

By: Aaron Bertrand   |   Comments (9)   |   Related: 1 | 2 | 3 | > Dates

Problem

I recently gave a presentation involving T-SQL, and one of the popular topics is always date formats. We had a conversation about how SQL Server handles various date formats depending on the user’s language settings. That SQL Server sometimes get this "wrong" – even YYYY-MM-DD, a big-endian and supposedly safe ISO-8601 format – surprised several people. I’ve talked about it here before, but let’s dig in a little.

Solution

The first time I saw SQL Server misinterpret a date, I thought for sure I had discovered a bug. While helping a colleague write a query, I jumped into their chair and took over Query Analyzer on their PC.

They had written something like this:

DECLARE @d datetime;
SET @d = '2001-08-13';

And they were seeing this error:

Převod datového typu varchar na datový typ datetime vrátil hodnotu mimo rozsah.

Which translates (well, roughly) to:

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

I looked at the code, then back at the message, then ran over to my own desk to try to reproduce the error. Of course, it worked fine on my computer. And if I had a nickel for every time I’ve heard that one…

So what was the problem?

Language. Certain languages coerce SQL Server into interpreting this specific format as YYYY-DD-MM instead of YYYY-MM-DD. You can reproduce this with the following code:

SET LANGUAGE Czech;
GO
DECLARE @d datetime;
SET @d = '2001-08-13';

Now, you might ask yourself, which languages are safe, and which are not? Well, my first response would be: Gravitate toward formats and methods that work in all scenarios. YYYYMMDD, for example, is safe for all data types, in all languages. Since you can’t control how someone’s settings are configured, or prevent any app from issuing a SET LANGUAGE command, it’s just safer to demand formats that are known to always work.

But you can get a more technical, less logical technical answer. SQL Server currently supports 34 languages, according to sys.syslanguages; though the documentation only lists 33. For some reason, Bokmål – Norway’s preferred written standard – is missing.

You can scan the list by querying the view:

SELECT name, alias, dateformat FROM sys.syslanguages ORDER BY alias;

Results:

Note that no language has a date format of ydm.

This list, on its own, doesn’t help me determine which languages are safe. One way to do that is to just run through that list, set the language to each alias, and try converting that same date I had trouble with so many years ago. We can build a dynamic SQL command from that same view, run it, and review the results.

SET NOCOUNT ON;
DROP TABLE IF EXISTS #lang;
CREATE TABLE #lang(name sysname, alias sysname, success bit);
DECLARE @sql nvarchar(max) = N'DECLARE @d datetime, @success bit;';
SELECT @sql += N'
  SET LANGUAGE ' + QUOTENAME(alias) + N';
  SET @success = CASE 
    WHEN TRY_CONVERT(datetime, ''2001-08-13'') IS NULL THEN 0 ELSE 1 END;
  INSERT #lang(name,alias,success) 
    VALUES(N''' + name + ''',''' + alias + ''',@success);'
FROM sys.syslanguages;
EXEC sys.sp_executesql @sql;
SELECT success, number = COUNT(*) FROM #lang GROUP BY success;

The summary shows that out of 34 languages, YYYY-MM-DD is an unsafe format in 24 languages, and safe only in 10.

We can break that down by not aggregating:

Results:

The next thing you might ask is why does SQL Server behave this way? I wish I had an authoritative answer for you, or even a plausible guess. Sadly, I’m not quite sure, though it dates back to the Sybase and maybe even Ashton-Tate days.

For a more complete list of date formats organized by country, see these resources:

• Date format by country (Wikipedia)
• Country Date Formats (Michael Connor)

And you’ll note that those lists, too, do not contain a single instance of ydm. Or any reason to believe any human being would ever mistake 2020-01-05 as May 1^st.

Next Steps

Make sure you are properly handling date formats and not making any assumptions about the user’s or application’s language or dateformat settings. Remember that you may not notice this issue until you misinterpret a date that actually breaks. For example, if you start testing on January 1^st and use today’s date as the literal, you might just not notice you are storing incorrect data – until the first error happens on January 13^th.

If you are accepting user input from a form, please use a calendar or date picker, so that you can have full control over the format that is passed to SQL Server. If you allow end users to type a date into a text field, you have zero confidence in the format they choose.

For more info on date handling in SQL Server, see these tips and videos:

• SQL Server DateTime Best Practices
• Recommended SQL Server Date Formats
• SQL Server Date Time Shorthand and Other Tricks
• SQL Server Date and Time Data Type Comparison
• All SQL Server date tips

About the author

Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and also blogs at sqlblog.org.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips