Início / dba / Perguntas / 166771
Accepted
Aaron Bertrand
Asked: 2017-03-10 18:47:32 +0800 CST

Quais formatos literais de data/hora são seguros para LANGUAGE e DATEFORMAT?

  • 772

É fácil demonstrar que muitos formatos de data/hora diferentes dos dois seguintes são vulneráveis ​​a interpretações errôneas devido a SET LANGUAGE, SET DATEFORMAT ou ao idioma padrão de um login:

yyyyMMdd                 -- unseparated, date only
yyyy-MM-ddThh:mm:ss.fff  -- date dash separated, date/time separated by T

Mesmo este formato, sem o T, pode parecer um formato ISO 8601 válido, mas falha em vários idiomas:

DECLARE @d varchar(32) = '2017-03-13 23:22:21.020';

SET LANGUAGE Deutsch;
SELECT CONVERT(datetime, @d);

SET LANGUAGE Français;
SELECT CONVERT(datetime, @d);

Resultados:

Die Spracheneinstellung wurde auf Deutsch geändert.

Msg 242, Level 16, State 3
Bei der Konvertierung eines varchar-Datentyps in einen datetime-Datentyp liegt der Wert außerhalb des gültigen Bereichs.

Le paramètre de langue est passé à Français.

Msg 242, Nível 16, Estado 3
La convert d'un type de données varchar en type de données datetime a créé une valeur hors limites.

Agora, estes falham como se, em inglês, eu tivesse transposto o mês e o dia, para formular um componente de data de yyyy-dd-mm:

DECLARE @d varchar(32) = '2017-13-03 23:22:21.020';

SET LANGUAGE us_english;
SELECT CONVERT(datetime, @d);

Resultado:

Msg 242, Level 16, State 3
A conversão de um tipo de dados varchar em um tipo de dados datetime resultou em um valor fora do intervalo.

(Este não é o Microsoft Access, que é "legal" para você e corrige a transposição para você. Além disso, erros semelhantes podem acontecer em alguns casos com SET DATEFORMAT ydm;- não é apenas uma coisa de idioma, esse é apenas o cenário mais comum em que esses quebras acontecem - e nem sempre são notadas porque às vezes não são erros, só que 7 de agosto virou 8 de julho e ninguém percebeu.)

Então, a pergunta:

Agora que sei que existem vários formatos inseguros, existem outros formatos que serão seguros, considerando qualquer combinação de idioma e formato de data?

sql-server t-sql

1 respostas

  1. Best Answer

    Na documentação , é declarado muito explicitamente que os únicos formatos seguros são aqueles que demonstrei no início da pergunta:

    yyyyMMdd                 -- unseparated, date only
yyyy-MM-ddThh:mm:ss.fff  -- date dash separated, date/time separated by T

    No entanto, foi recentemente trazido à minha atenção que existe um terceiro formato que é igualmente imune a qualquer configuração de idioma ou formato de data:

    yyyyMMdd hh:mm:ss.fff    -- unseparated date, no T separator

    TL;DR: Isso é verdade. Para datetimee smalldatetime.

    Continue lendo para a versão mais longa e sobre o máximo de provas que você obterá.

    Há uma brecha que explica isso - enquanto o corpo do texto principal não reconhece yyyyMMdd hh:...como um formato seguro contra interpretações de formato de data ou linguagem transposta, há uma pequena sinopse que diz que a parte de data de tal string não é validada dependendo das configurações de formato de data:

    insira a descrição da imagem aqui

    É bastante diferente de mim apenas levar a documentação ao pé da letra, geralmente. Você pode dizer que sou um pouco cético. E a linguagem também é ambígua aqui - apenas afirma que se trata da combinação de data e hora, não chamando o espaço explicitamente (que poderia ser um retorno de carro, pelo que sei). Ele também diz que não é multilíngue, o que significa que pode falhar em certos idiomas, mas descobriremos em breve que isso também está incorreto.

    Então, decidi provar que nenhuma combinação de idioma/formato de data poderia fazer esse formato específico falhar.

    Primeiro, criei um pequeno bloco de SQL dinâmico para cada linguagem:

    EXEC sys.sp_executesql @sql, N'@lang sysname', N'us_english';

    Isso produziu 34 linhas de saída como esta:

    EXEC sys.sp_executesql @sql, N'@lang sysname', N'us_english';
EXEC sys.sp_executesql @sql, N'@lang sysname', N'Deutsch';
EXEC sys.sp_executesql @sql, N'@lang sysname', N'Français';
EXEC sys.sp_executesql @sql, N'@lang sysname', N'日本語';
...
EXEC sys.sp_executesql @sql, N'@lang sysname', N'简体中文';
EXEC sys.sp_executesql @sql, N'@lang sysname', N'Arabic';
EXEC sys.sp_executesql @sql, N'@lang sysname', N'ไทย';
EXEC sys.sp_executesql @sql, N'@lang sysname', N'norsk (bokmål)';

    Copiei essa saída para uma nova janela de consulta e, acima dela, gerei este código, que espero tentar converter essa mesma data (13 de março) para o 3º dia do 13º mês em pelo menos um caso:

    DECLARE @sql nvarchar(max) = N'
SET LANGUAGE @lang;
SET DATEFORMAT ydm;
SELECT @@LANGUAGE, CONVERT(datetime, ''20170313 23:22:21.020'');';

    Não, todos os idiomas funcionaram bem no ydm. Eu tentei todos os outros formatos também e também todos os tipos de dados de data/hora. 34 conversões bem-sucedidas até 13 de março, todas as vezes.

    Então, eu admito a @AndriyM e @ErikE que, de fato, existe um terceiro formato seguro. Vou manter isso em mente para postagens futuras, mas já bati os tambores sobre os outros dois em tantos lugares, não vou caçá-los todos e corrigi-los agora.

    Por extensão, você pensaria que este seria seguro, mas não:

    yyyyMMddThh:mm:ss.fff    -- unseparated date, T separator

    Eu acho que em todos os idiomas, isso produzirá o equivalente a:

    Msg 241, Level 16, State 1, Line 8
    A conversão falhou ao converter a data e/ou hora da cadeia de caracteres.

    Para completar, há um quarto formato seguro, mas só é seguro para conversões para os tipos de data/hora mais recentes ( date, datetime2, datetimeoffset). Nesses casos, as configurações de idioma não podem interferir:

    yyyy-MM-dd hh:mm:...

    No entanto, eu recomendo contra o seu uso porque só funciona para os tipos mais recentes, e os antigos ainda estão em uso massivo, na minha experiência. Por que ter os traços lá quando em qualquer outro lugar (ou de fato no mesmo código, se o tipo de dados mudar) você precisa removê-los?

    SET LANGUAGE Deutsch;
DECLARE @dashes char(10) = '2017-03-07 03:34';
DECLARE @d date = @dashes, @dt datetime = @dashes, @dt2 datetime2 = @dashes;

SELECT DATENAME(MONTH,@d), DATENAME(MONTH,@dt), DATENAME(MONTH,@dt2);

    Mesmo com a mesma string de origem, as conversões geraram resultados bastante diferentes:

    März    Juli    März

    O formato que funciona para datetime ( yyyyMMdd) também funcionará sempre para date e os outros novos tipos. Então, IMHO, sempre use isso. E dado o terceiro formato para tipos com data/hora ( yyyyMMdd hh:...), isso realmente permitirá que você seja mais consistente - mesmo que o componente de data seja sempre um pouco menos legível.

    Agora, levarei apenas alguns anos, mais ou menos, para adquirir o hábito de demonstrar os três formatos seguros quando estou falando sobre representação de datas em strings.

    • 31

relate perguntas