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

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


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.


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:

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;


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.

CREATE TABLE #lang(name sysname, alias sysname, success bit);
DECLARE @sql nvarchar(max) = N'DECLARE @d datetime, @success bit;';
SELECT @sql += 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:


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 Ashton-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:

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

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,, and also blogs at

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

View all my tips

Comments For This Article

Wednesday, March 27, 2024 - 10:44:34 AM - Aaron Bertrand Back To Top (92130)
@Jenda I agree it doesn’t make sense. I’m just trying to share how SQL Server works since it is so unintuitive. They fixed this behavior with the newer types like date and datetime2, but we’ll be stuck with datetime and smalldatetime for years to come. For me, it’s more important to use consistent format instead of trying to remember when it will work, when it won’t, and protecting yourself from someone changing a column or parameter from date to datetime, for example.

Friday, March 22, 2024 - 5:33:53 PM - Jenda Back To Top (92108)
This makes no sense whatsoever. I swear that no Czech ever wrote any date in the yyyy-dd-mm format and each and every single one would assume 2024-21-05 is a typo. The official pages of the Czech Language Institute (the language standard body) state clearly that the official format is 21. 5. 2024 or 21. května 2024, that format 21.05.2024 (with leading zeroes and without spaces) is also acceptable and that the leading zeroes must be used when writing the dates in "descending order. That is 2024-05-21, not 2024-5-21. ( - WARNING! If you look at the automatically translated version, then even some of the example dates seem to get translated)

The other weird thing is that this only affects the datetime type. Not the date or datetime2.

DECLARE @Date DATE = '2021-02-11'
DECLARE @Datetime DATETIME = '2021-02-11'
DECLARE @Datetime2 DATETIME2 = '2021-02-11'
SELECT @Date as [Date], @Datetime as [Datetime], @Datetime2 as [Datetime2]

Tuesday, March 21, 2023 - 11:09:21 AM - Aaron Bertrand Back To Top (91032)
That I couldn't find them shows that it is not very discoverable (or I'm very bad at searching).

In any case, I wonder why {d 'yyyy-MM-dd'} is "easier" than just removing the dashes and using a string literal that is more universally supported? e.g. 'yyyyMMdd'

Tuesday, March 21, 2023 - 8:38:21 AM - Davide Back To Top (91031)

I have found them here

Tuesday, March 21, 2023 - 7:04:17 AM - Aaron Bertrand Back To Top (91030)
Davide, that form is not even documented (so not sure if it is even officially supported), and it is not self-documenting, either. But hey, if it works for you, great!

Here are the ODBC scalar functions that are documented and supported:

Monday, March 20, 2023 - 8:39:49 AM - Davide Back To Top (91024)
Much more easy...

SET @d={d '2023-05-01'}

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:

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:

DECLARE @d datetime;

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

get free sql tips
agree to terms