Início / dba / Perguntas / 208741
Accepted
Jonathan Fite
Asked: 2018-06-05 13:03:45 +0800 CST

O que 0 em DATEDIFF(MINUTE, 0, <Date>) realmente significa?

  • 772

Então, nossa equipe de dados pediu ajuda para resolver um problema que eles tinham. Acabei rastreando alguns dados realmente fora de alcance (1/1/0001) e uma função DATEDIFF que eles estavam usando. Enquanto eu resolvi o problema deles, aconteceu que eu realmente não sei o que o 0 se transforma quando usado como eles o usavam.

Eu originalmente pensei que estava mais perto de um estouro de número inteiro em vez de um verdadeiro erro de conversão, mas não é isso. Eu tentei em uma caixa SQL 2016 com DATEDIFF_BIG e mesmo erro. Eu tenho uma amostra para vocês abaixo para brincarem com o que funciona e o que não funciona. 

/** Setup The Sample */
DECLARE @TestValue DATETIME2(7)
SET @TestValue = '0001-01-01 10:30:00.0000000'

/** Conversion Error 
Msg 242, Level 16, State 3, Line 10
The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value.
*/
SELECT DATEDIFF(MINUTE, 0, @TestValue)

--Also does not work, same error.
SELECT DATEDIFF_BIG(MINUTE, 0, @TestValue)

/** Works */
SELECT DATEDIFF(MINUTE, '1/1/1900', @TestValue)

/** Works */
SELECT DATEDIFF(MINUTE, CAST(0 AS DATETIME), @TestValue)

/** Doesn't Work, you can't cast 0 to a DATETIME2 */
--SELECT DATEDIFF(MINUTE, CAST(0 AS DATETIME2), @TestValue)

/** Works (or no error, which is fine)*/
SELECT DATEDIFF(MINUTE, 0, TRY_CAST(@TestValue AS DATETIME))

Pergunta bônus, já que 0 não funciona em todos os casos para DATETIME2, qual é a alternativa?

O QUE DECIDIMOS FAZER

Então, comecei a recomendar que minha equipe faça o seguinte, já que você vê 0 em muitos exemplos de datemath (primeiro dia do mês etc.). Portanto, recomendo que você faça uma conversão explícita de 0 para datetime e continue como desejar. Isso evitará o erro enquanto ainda estiver funcionando. Então:

DATEDIFF(MINUTE, CAST(DATETIME, 0), <Date>)
sql-server-2016 datetime

1 respostas

  1. Best Answer

    Você pode ver o que está acontecendo adicionando a expressão a uma consulta com uma FROMcláusula e observando o escalar de computação.

    Isso mostra o seguinte.

    +---------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------+
|                          Expression                           |                                                       Evaluated As                                                        |
+---------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------+
| DATEDIFF(MINUTE, 0, @TestValue)                               | Scalar Operator(datediff(minute,'1900-01-01 00:00:00.000',CONVERT_IMPLICIT(datetime,[@TestValue],0)))                     |
| DATEDIFF_BIG(MINUTE, 0, @TestValue)                           | Scalar Operator(datediff_big(minute,'1900-01-01 00:00:00.000',CONVERT_IMPLICIT(datetime,[@TestValue],0)))                 |
| DATEDIFF(MINUTE, '1/1/1900', @TestValue)                      | Scalar Operator(datediff(minute,'1900-01-01 00:00:00.0000000 +00:00',CONVERT_IMPLICIT(datetimeoffset(7),[@TestValue],0))) |
| DATEDIFF(MINUTE, CAST(0 AS DATETIME), @TestValue)             | Scalar Operator(datediff(minute,'1900-01-01 00:00:00.000 +00:00',CONVERT_IMPLICIT(datetimeoffset(7),[@TestValue],0)))     |
| DATEDIFF(MINUTE, CAST('1900-01-01' AS DATETIME2), @TestValue) | Scalar Operator(datediff(minute,'1900-01-01 00:00:00.0000000 +00:00',CONVERT_IMPLICIT(datetimeoffset(7),[@TestValue],0))) |
| DATEDIFF(MINUTE, 0, TRY_CAST(@TestValue AS DATETIME))         | Scalar Operator(datediff(minute,'1900-01-01 00:00:00.000',TRY_CAST([@TestValue] AS datetime)))                            |
+---------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------+

    Se você passar um literal 0para esta função, ele sempre será convertido implicitamente em datetime.

    Lançar um intpara datetimeretorna 1900-01-01 + <int> daysassim 1900-01-01.

    O problema para você é para que tipo de dados o terceiro parâmetro é convertido. @TestValueé de datetime2- quando você passa um inteiro, ambos os lados são convertidos implicitamente para datetime.

    '0001-01-01 10:30:00.0000000'está fora do intervalo para datetime, portanto, o erro.

    Nos momentos em que é bem-sucedido, os parâmetros são lançados paradatetimeoffset(7)

    • 7

relate perguntas