How SQL Server handles the date format YYYY-MM-DD
By: Aaron Bertrand | Updated: 2021-03-04 | Comments (7) | Related: More > 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
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
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
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
the documentation only lists 33. For some reason,
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;
Note that no language has a date format of
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:
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
ydm. Or any reason to believe any human being would
ever mistake 2020-01-05 as May 1st.
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 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
View all my tips
Article Last Updated: 2021-03-04