Início / dba / Perguntas / 71150
Accepted
Simon Hughes
Asked: 2014-07-11 01:44:12 +0800 CST

Comparação SQL Datetime/datetimeoffset(2) e estranheza de elenco implícito

  • 772

Descobri um problema ao inserir dados em nosso banco de dados. Minha instrução de inserção estava verificando a existência de dados na cláusula WHERE para evitar a inserção de dados duplicados. Nenhum foi detectado e o INSERT aconteceu. No entanto, a restrição exclusiva rejeitou os dados porque já existiam no banco de dados.

O problema era que os dados a serem inseridos eram DATETIMEOFFSET(2) e o campo do banco de dados inserido era DATETIME.

Para mostrar que você quer que eu esteja falando, execute o seguinte:

DECLARE @dt  DATETIME          = '2014-07-07 09:49:33.000';
DECLARE @dto DATETIMEOFFSET(2) = '2014-07-07 09:49:33.00 +07:00';

PRINT CASE WHEN @dt = @dto THEN 'Equals matches'
           ELSE 'Equals does not match'
      END

PRINT CASE WHEN @dt = CAST(@dto AS DATETIME) THEN 'Cast matches'
           ELSE 'Cast does not match'
      END

Ele imprime:

  • Igual não corresponde
  • Transmitir partidas

O operador de comparação (=) não funciona da mesma forma que a conversão implícita se você inserir os dados. Na verdade, o operador cast/convert descarta o deslocamento! Loucura.

Por que o operador de comparação funciona de maneira diferente da conversão implícita que ocorre durante um INSERT?

sql-server

2 respostas

  1. Best Answer

    Parece que o oposto é verdadeiro: a conversão implícita leva o deslocamento para a equação, mas as funções cast/convert não.

    DECLARE @dt  DATETIME          = '2014-07-07 02:49:33.000';
DECLARE @dto DATETIMEOFFSET(2) = '2014-07-07 09:49:33.000 +07:00';

PRINT CASE WHEN @dt = @dto THEN 'Equals matches'
           ELSE 'Equals does not match'
      END

PRINT CASE WHEN @dt = CAST(@dto AS DATETIME) THEN 'Cast matches'
           ELSE 'Cast does not match'
      END

PRINT CASE WHEN @dt = CONVERT(DATETIME,@dto) THEN 'Convert matches'
           ELSE 'Convert does not match'
      END

    A comparação (deduzidas 7 horas de @dt) resulta em:

    • Partidas iguais
    • O elenco não corresponde
    • Converter não corresponde

    Fiz mais algumas investigações e me deparei com este artigo.

    "Quando você converte de datetime2 ou datetimeoffset para date, não há arredondamento e a parte da data é extraída explicitamente. Para qualquer conversão implícita de datetimeoffset para data, hora, datetime2, datetime ou smalldatetime, a conversão é baseada na data e hora locais valor."

    Então, quando você quiser tratar '2014-07-07 09:49:33.000' e '2014-07-07 09:49:33.000 +07:00' como iguais, sua única opção é fazer uma conversão explícita via cast ou converter. Como as conversões implícitas só funcionariam quando o deslocamento do fuso horário do seu servidor fosse o mesmo que o deslocamento especificado em @dto.

    • 10

  2. Bem, a comparação e o CAST funcionam de maneira diferente.

    A comparação usa precedência de tipo para determinar como comparar tipos diferentes. DATETIMEOFFSETestá em 4º lugar, DATETIMEe DATETIME2em 6º e 5º lugar respectivamente. Portanto, o DATETIMEargumento é convertido (usando CAST, veja abaixo) para DATETIMEOFFSETprimeiro.

    A conversão paraDATETIMEOFFSET pode ser alcançada de três maneiras possíveis (talvez mais?):

    • CASTque adiciona fuso horário "+00:00" (UTC)
    • CONVERTque adiciona fuso horário "+00:00" (UTC)
    • AT TIMEZONEque adiciona qualquer fuso horário que você fornecer como o argumento do lado direito

    Infelizmente, nunca encontrei nenhuma documentação sobre o comportamento de CASTnem CONVERTquando "upscaling" para DATETIMEOFFSET. Portanto, esse comportamento pode diferir entre máquinas e versões.

    Por outro lado, a conversão para DATETIME corta a parte "deslocada".

    Então, no seu caso:

    @dt = @dto
  -- @dt gets converted to DATETIMEOFFSET(2)
  -- you are comparing:
  -- '2014-07-07 09:49:33.000 +00:00' = '2014-07-07 09:49:33.00 +07:00'

@dt = CAST(@dto AS DATETIME)
  -- botha are DATETIME, but @dto gets "downscaled" to DATETIME
  -- you are comparing:
  -- '2014-07-07 09:49:33.000' = '2014-07-07 09:49:33.00'
    • 2

relate perguntas