I have a French installation of SQL Server using French_CI_AS
Collation. For legacy reasons, a table column stores data as VARCHAR
which is later converted to the right type, akin to variant in C++.
Two rows in this column have data as:
2020-10-12 22:54:40
2020-10-13 04:42:10
The second date fails when converting to DATETIME
with default options. The application uses ODBC escape sequences. SQL such as COLMVALUE >= {ts'2020-01-01 00:00:00'}
fails with the error:
La conversion d'un type de données nvarchar en type de données datetime a créé une valeur hors limites
I did a DBCC useroptions
and the server has date format set to dmy
. Why is the 2nd row failing? It looks like it is trying to interpret "13" as the month. If the format is dmy
, shouldn't both dates fail?
Also, it is interesting to note that on this server, the following fails:
DECLARE @datevar DATETIME = '2020-10-13 04:42:10';
SELECT @datevar;
But this works:
DECLARE @datevar DATETIME2 = '2020-10-13 04:42:10';
SELECT @datevar;
I am aware that DATETIME2
is more accurate and has greater range than DATETIME
. However, it seems that even the parsing is different for the two here.
Best Answer
This is not a collation issue. This is a language / locale / culture issue. Your DB (or your login's default language) is set to "French" (most likely) and they use Day then Month formats, not Month then Day, hence your statement is attempting to use month 13.
Try this to reproduce:
You have two options for changing this:
SET LANGUAGE
This controls not just the date format but also currency symbol, day and month names, etc. This might not be the best choice given that it controls more than just the date format, but it might be easier if it only requires updating the login's default language instead of updating a lot of code to do option # 2.
SET DATEFORMAT
This controls just the date format:
Regarding your other questions:
and
All of that is because working with dates is really messy. The documentation for
DATEFORMAT
states not only that:but also:
While the documentation doesn't tie those two statements together, testing does show that ISO 8601 formatted dates (e.g. YYYY-MM-DD) are "interpreted independently of the DATEFORMAT setting" only for
DATE
,DATETIME2
, andDATETIMEOFFSET
. That is why the date of'2020-10-13'
respects the value ofDATEFORMAT
for yourDATETIME
variable (and hence receives an error for cultures where the day comes before the month, whether separating date parts with "-" or "/"), but is parsed correctly for yourDATETIME2
test (and always will regardless of the current culture or value ofDATEFORMAT
).P.S. I would not consider a variant /
SQL_VARIANT
/object
, etc to be the "right" type ;-)