SQL Server com conversão de data de agrupamento French_CI_AS

Question

Eu tenho uma instalação francesa do SQL Server usando French_CI_ASCollation. Por motivos de legado, uma coluna de tabela armazena dados como os VARCHARquais são posteriormente convertidos para o tipo certo, semelhante à variante em C ++.

Duas linhas nesta coluna têm dados como:

2020-10-12 22:54:40

2020-10-13 04:42:10

A segunda data falha ao converter para DATETIMEas opções padrão. O aplicativo usa sequências de escape ODBC. SQL como COLMVALUE >= {ts'2020-01-01 00:00:00'}falha com o erro:

A conversão de um tipo de données nvarchar em tipo de données datetime a créé une valeur hors limites

Fiz um DBCC useroptionse o servidor tem o formato de data definido como dmy. Por que a 2ª linha está falhando? Parece que está tentando interpretar "13" como o mês. Se o formato for dmy, as duas datas não deveriam falhar?

Além disso, é interessante notar que neste servidor, o seguinte falha:

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

Mas isso funciona:

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

Estou ciente de que DATETIME2é mais preciso e tem maior alcance do que DATETIME. No entanto, parece que até a análise é diferente para os dois aqui.

Answer 1

Este não é um problema de agrupamento. Este é um problema de idioma / local / cultura. Seu banco de dados (ou o idioma padrão de seu login) é definido como "francês" (provavelmente) e eles usam formatos de dia e mês, não mês e dia, portanto, seu extrato está tentando usar o mês 13.

Experimente isto para reproduzir:

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

Você tem duas opções para alterar isso:

1. DEFINIR IDIOMA
   Controla não apenas o formato da data, mas também o símbolo da moeda, nomes do dia e mês, etc. Esta pode não ser a melhor escolha, visto que controla mais do que apenas o formato da data, mas pode ser mais fácil se exigir apenas a atualização do idioma padrão do login em vez de atualizar muito código para fazer a opção # 2.

2. SET DATEFORMAT
   Isso controla apenas o formato da data:

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

 
Sobre suas outras perguntas:

Se o formato for dmy, as duas datas não deveriam falhar?

e

(implícito) Por que DATETIMEe DATETIME2parecem analisar a mesma data de maneira diferente?

Tudo isso porque trabalhar com datas é muito complicado. A documentação para DATEFORMATestados não apenas que:

as interpretações datetime e smalldatetime podem não corresponder a date , datetime2 ou datetimeoffset

mas também:

Alguns formatos de sequência de caracteres, por exemplo ISO 8601, são interpretados independentemente da configuração DATEFORMAT.

Enquanto a documentação não amarrar essas duas instruções juntos, testes mostra que ISO 8601 formatado datas (por exemplo, AAAA-MM-DD) são "interpretadas independentemente da configuração DATEFORMAT" apenas para DATE, DATETIME2e DATETIMEOFFSET. É por isso que a data de '2020-10-13'respeita o valor de DATEFORMATpara sua DATETIMEvariável (e, portanto, recebe um erro para culturas onde o dia vem antes do mês, seja separando as partes da data com "-" ou "/"), mas é analisada corretamente para o seu DATETIME2teste (e sempre será, independentemente da cultura atual ou valor de DATEFORMAT).

PS Eu não consideraria uma variante / SQL_VARIANT/ object, etc como o tipo "certo" ;-)