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


By:   |   Updated: 2021-03-04   |   Comments (3)   |   Related: More > 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:

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

query results

We can break that down by not aggregating:

Results:

query 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 Ahston-Tate days.

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

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 1st.

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 1st 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 13th.

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:



Last Updated: 2021-03-04


get scripts

next tip button



About the author
MSSQLTips author Aaron Bertrand 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.

View all my tips



Comments For This Article




Thursday, March 4, 2021 - 5:25:55 PM - Joe F Celko Back To Top (88339)
There are times I think I'm the only person that reads the ANSI standards. Maybe that's because they had to vote on them so many years ago when I was on the committee. The only format permitted in the ANSI/ISO standards is the 8601 version with dashes. We did this very deliberately to make dates uniform and easy to parse because it's not ambiguous. This is why Microsoft defaults to this format in its current implementations and will eventually get rid of the old local dialects. Another reason we picked it is that it is the most common, embedded display format among all the national options. When reviewing code in my consulting work, this is one of the tells, they look for so I can spot an inexperienced SQL programmer. It tells me he's going to make some other local dialect mistakes.

Thursday, March 4, 2021 - 9:49:35 AM - Aaron Bertrand Back To Top (88334)
Thanks Andrew, I talk about the format you _should_ use in this quick video:

https://www.mssqltips.com/sqlservertutorial/9315/recommended-sql-server-date-formats/

But I don't know that there is any reason to use nvarchar in this scenario. What Unicode characters are you expecting to protect?

Thursday, March 4, 2021 - 9:27:44 AM - Andrew Schickedanz Back To Top (88333)
One way to avoid this error in all languages is specify the date as an NVARCHAR string in full ISO 8601 format.

Try this:

SET LANGUAGE Arabic;
GO
DECLARE @d datetime;

SET @d = N'2001-08-13T00:00:00';


download





Recommended Reading

Date and Time Conversions Using SQL Server

Add and Subtract Dates using DATEADD in SQL Server

Format SQL Server Dates with FORMAT Function

SQL Server Date and Time Functions with Examples

SQL Convert Date to YYYYMMDD














get free sql tips
agree to terms