SQL Server mit French_CI_AS Konvertierung des Sortierdatums

Question

Ich habe eine französische Installation von SQL Server mit French_CI_ASCollation. Aus alten Gründen werden in einer Tabellenspalte Daten gespeichert, VARCHARdie später in den richtigen Typ konvertiert werden, ähnlich einer Variante in C ++.

Zwei Zeilen in dieser Spalte enthalten folgende Daten:

2020-10-12 22:54:40

2020-10-13 04:42:10

Das zweite Datum schlägt bei der Konvertierung DATETIMEmit Standardoptionen fehl . Die Anwendung verwendet ODBC-Escape-Sequenzen. SQL wie COLMVALUE >= {ts'2020-01-01 00:00:00'}schlägt mit dem Fehler fehl:

Die Umstellung auf den Typ des Données nvarchar und den Typ des Données datetime a créé une valeur hors limites

Ich habe ein DBCC useroptionsund der Server hat Datumsformat eingestellt dmy. Warum fällt die 2. Reihe aus? Es sieht so aus, als würde versucht, "13" als Monat zu interpretieren. Wenn das Format ist dmy, sollten nicht beide Daten fehlschlagen?

Es ist auch interessant festzustellen, dass auf diesem Server Folgendes fehlschlägt:

DECLARE @datevar DATETIME = '2020-10-13 04:42:10';    
SELECT @datevar;  

Aber das funktioniert:

DECLARE @datevar DATETIME2 = '2020-10-13 04:42:10';    
SELECT @datevar;  

Mir ist bewusst, dass dies DATETIME2genauer ist und eine größere Reichweite hat als DATETIME. Es scheint jedoch, dass sogar die Analyse für die beiden hier unterschiedlich ist.

Answer 1

Dies ist kein Sortierproblem. Dies ist ein Sprach- / Gebietsschema- / Kulturproblem. Ihre Datenbank (oder die Standardsprache Ihres Logins) ist (höchstwahrscheinlich) auf "Französisch" eingestellt und verwendet die Formate Tag und Monat, nicht Monat und Tag. Daher versucht Ihre Anweisung, Monat 13 zu verwenden.

Versuchen Sie dies zu reproduzieren:

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

Sie haben zwei Möglichkeiten, dies zu ändern:

1. SPRACHE EINSTELLEN
   Hiermit wird nicht nur das Datumsformat, sondern auch das Währungssymbol, die Namen von Tagen und Monaten usw. gesteuert. Dies ist möglicherweise nicht die beste Wahl, da mehr als nur das Datumsformat gesteuert wird. Es ist jedoch möglicherweise einfacher, wenn nur das aktualisiert werden muss Die Standardsprache des Logins, anstatt viel Code zu aktualisieren, um Option 2 auszuführen.

2. SET DATEFORMAT
   Hiermit wird nur das Datumsformat gesteuert :

   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
   */
   

 
Zu Ihren anderen Fragen:

Wenn das Format ist dmy, sollten nicht beide Daten fehlschlagen?

und

(implizit) Warum DATETIMEund DATETIME2scheint das gleiche Datum anders zu analysieren?

All das liegt daran, dass die Arbeit mit Daten wirklich chaotisch ist. In der Dokumentation für DATEFORMATheißt es nicht nur:

Die Interpretationen von datetime und smalldatetime stimmen möglicherweise nicht mit Datum , datetime2 oder datetimeoffset überein

aber auch:

Einige Zeichenfolgenformate, z. B. ISO 8601, werden unabhängig von der Einstellung DATEFORMAT interpretiert.

Während die Dokumentation zusammen nicht diese beiden Aussagen bindet, machen Tests zeigen , dass ISO 8601 formatierten Daten (zB YYYY-MM-DD) „unabhängig von der Einstellung interpretiert DATEFORMAT“ ist nur für die DATE, DATETIME2, und DATETIMEOFFSET. Aus diesem Grund '2020-10-13'respektiert das Datum den Wert von DATEFORMATfür Ihre DATETIMEVariable (und erhält daher einen Fehler für Kulturen, in denen der Tag vor dem Monat liegt, unabhängig davon, ob Datumsteile mit "-" oder "/" getrennt werden), wird jedoch für Ihren DATETIME2Test korrekt analysiert (und wird es immer tun, unabhängig von der aktuellen Kultur oder dem Wert von DATEFORMAT).

PS Ich würde eine Variante / SQL_VARIANT/ objectusw. nicht als den "richtigen" Typ betrachten ;-)