SQL Server with French_CI_AS Collation date conversion

Question

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.

Answer 1

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:

SET LANGUAGE French;
DECLARE @datevar DATETIME = '2020-10-13 04:42:10';
/*
Le paramètre de langue est passé à Français.
Msg 242, Level 16, State 3, Line XXXXX
La conversion d'un type de données varchar en type de données datetime a
    créé une valeur hors limites.
*/

GO
PRINT '-----';
GO

SET LANGUAGE English;
DECLARE @datevar DATETIME = '2020-10-13 04:42:10';
GO

You have two options for changing this:

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

2. SET DATEFORMAT
   This controls just the date format:

   SET LANGUAGE French;
   DECLARE @datevar DATETIME = '2020-10-13 04:42:10';
   /*
   Le paramètre de langue est passé à Français.
   Msg 242, Level 16, State 3, Line XXXXX
   La conversion d'un type de données varchar en type de données datetime a
       créé une valeur hors limites.
   */
   
   SET DATEFORMAT ymd;
   DECLARE @datevar2 DATETIME = '2020-10-13 04:42:10';
   
   SELECT @datevar AS [@datevar], @datevar2 AS [@datevar2];
   /*
   @datevar    @datevar2
   NULL        2020-10-13 04:42:10.000
   */
   

 
Regarding your other questions:

If the format is dmy, shouldn't both dates fail?

and

(implied) Why do DATETIME and DATETIME2 appear to parse the same date differently?

All of that is because working with dates is really messy. The documentation for DATEFORMAT states not only that:

datetime and smalldatetime interpretations may not match date, datetime2, or datetimeoffset

but also:

Some character string formats, for example ISO 8601, are interpreted independently of the DATEFORMAT setting.

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, and DATETIMEOFFSET. That is why the date of '2020-10-13' respects the value of DATEFORMAT for your DATETIME 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 your DATETIME2 test (and always will regardless of the current culture or value of DATEFORMAT).

P.S. I would not consider a variant / SQL_VARIANT / object, etc to be the "right" type ;-)