Arredondamento DATEDIFF

Esse é um comportamento esperado/documentado?

Isso só é esperado se você estiver familiarizado com as regras, mas elas nunca foram totalmente documentadas .

Este é particularmente o caso para o legado datetimee smalldatetimetipos, que têm uma série de peculiaridades mantidas para compatibilidade com versões anteriores. Os tipos mais modernos como datetime2não permitem estranhezas como adicionar números a datas ou converter implicitamente inteiros. Muitas das peculiaridades são o resultado de decisões questionáveis ​​do passado.

Se quiser evitar surpresas, seja sempre explícito sobre os tipos de dados.

A desleixo de tipo é abundante no código original. Strings não são tipos de data/hora, elas são strings. CASTnão pode aceitar um parâmetro de estilo (ao contrário de CONVERT), o que pode resultar em ambiguidade, resultados não determinísticos ou até mesmo erros de tempo de execução. Usar DATEDIFFcom uma mistura de tipos de dados é apenas pedir problemas.

Exemplo

Vamos pegar o exemplo de Jonathan Fite porque é mais simples que o original:

SELECT
    v.dt,
    UsingZero = DATEDIFF(DAY, 0, v.dt),
    ConvertToDateTime = DATEDIFF(DAY, CONVERT(datetime, 0), v.dt),
    MishMash = DATEDIFF(DAY, CONVERT(datetime, 0), CONVERT(datetime2(3), v.dt))
FROM
(
    VALUES
        ('2024-08-17 23:59:59.997'),
        ('2024-08-17 23:59:59.998'),
        ('2024-08-17 23:59:59.999'),
        ('2024-08-18 00:00:00.000')
) AS v (dt);

Os itens na VALUEScláusula são strings. Eles podem parecer datas e horas para um humano, mas não são.

As expressões na SELECTcláusula estão à mercê das regras misteriosas para conversão de tipo de dados executadas pelas DATEDIFFimplementações (sim, existem várias).

1.UsingZero = DATEDIFF(DAY, 0, v.dt)

Aqui chamamos DATEDIFFcom um inteiro e uma varcharstring como parâmetros.

O SQL Server converte tanto o zero quanto a dtstring para datetime, como você pode ver no plano de execução:

Expr1005 = datediff(day,'1900-01-01 00:00:00.000',
  CONVERT_IMPLICIT(datetime,[Union1004],0))

Union1004é o rótulo dado às varcharstrings da VALUEScláusula. O literal '1900-01-01 00:00:00.000' é o resultado dobrado constante da conversão de zero para datetime. Observe que o formato da string exibida é datetime-specific (ela tem três decimais fracionários).

A conversão implícita de suas strings para datetimeé a parte importante para explicar sua preocupação com arredondamento:

SELECT
    v.dt,
    AsDateTime = CONVERT(datetime, v.dt, 121)
FROM
(
    VALUES
        -- These are strings
        ('2024-08-17 23:59:59.997'),
        ('2024-08-17 23:59:59.998'),
        ('2024-08-17 23:59:59.999'),
        ('2024-08-18 00:00:00.000')
) AS v (dt);

Observe o arredondamento devido à precisão limitada de datetime(1/300s).

Não é o zero que causa o arredondamento, é a conversão implícita de suas strings para datetime.

2.ConvertToDateTime = DATEDIFF(DAY, CONVERT(datetime, 0), v.dt)

Agora fornecemos DATEDIFFum datetimevalor e uma varcharstring.

O SQL Server converte ambos para datetimeoffset, como você pode ver no plano de execução:

Expr1006 = datediff(day,'1900-01-01 00:00:00.000 +00:00',
  CONVERT_IMPLICIT(datetimeoffset(7),[Union1004],0))

O literal '1900-01-01 00:00:00.000 +00:00' é o resultado dobrado constante da conversão de zero para datetimeoffset(3). O formato da string exibida tem um deslocamento de fuso horário e três casas decimais. Sua string é convertida implicitamente para datetimeoffset(7).

Nenhum arredondamento ocorre neste caso porque o zero mapeia para um valor de data exato e todas as strings são exatamente representáveis.

3.MishMash = DATEDIFF(DAY, CONVERT(datetime, 0), CONVERT(datetime2(3), v.dt))

Por fim, fornecemos DATEDIFFum datetimee o resultado da conversão das varcharstrings para datetime2(3)(com um estilo padrão).

O plano de execução mostra o cast explícito para datetimeconstant-folded para datetimeoffset(3)e exibido com os três decimais esperados e um deslocamento de fuso horário. Suas varcharstrings são primeiro cast explicitamente para datetime2(3)conforme solicitado pelo código, então implicitamente convertidas para datettimeoffset(3):

Expr1007 = datediff(day,'1900-01-01 00:00:00.000 +00:00',
  CONVERT_IMPLICIT(datetimeoffset(3),CONVERT(datetime2(3),[Union1004],0),0))

Provavelmente não era isso que pretendiam.

Sendo explícito

Você não disse qual era o tipo de dado dos valores de origem, mas tendo em mente DATEDIFFos usos datetimeoffsetinternos para a maioria dos cálculos, podemos muito bem escolher:

SELECT
    V.dt,
    Typed =
        DATEDIFF
        (
            DAY,
            -- Explicit type
            CONVERT(datetimeoffset(3), '1900-01-01T00:00:00.000Z', 127),
            V.dt
        )
FROM
(
    VALUES
        -- Explicit types
        (CONVERT(datetimeoffset(3), '2024-08-17T23:59:59.997Z', 127)),
        (CONVERT(datetimeoffset(3), '2024-08-17T23:59:59.998Z', 127)),
        (CONVERT(datetimeoffset(3), '2024-08-17T23:59:59.999Z', 127)),
        (CONVERT(datetimeoffset(3), '2024-08-18T00:00:00.000Z', 127))
) AS V (dt);

Resultados:

O plano de execução mostra:

[Expr1005] = datediff(day,'1900-01-01 00:00:00.000 +00:00',[Union1004])

Não é tão fácil de ver, mas os [Union1004]valores também têm o tipo correto:

('2024-08-17 23:59:59.997 +00:00'), 
('2024-08-17 23:59:59.998 +00:00'), 
('2024-08-17 23:59:59.999 +00:00'), 
('2024-08-18 00:00:00.000 +00:00')

Você pode escolher datetime2(3)ou qualquer coisa para corresponder exatamente ao tipo de dado de origem, mas então haverá uma conversão implícita no datetimeoffsetplano porque é isso que DATEDIFFé usado internamente nesse caso.

Conselho

Escolha um estilo determinístico apropriado para conversões de data/hora de strings, prefira CONVERTover CAST(que não aceita um estilo) e seja explícito sobre seus tipos de dados se não gostar de resultados confusos e situações difíceis de depurar em geral.

Fiz uma pergunta relevante aqui. O que 0 em DATEDIFF(MINUTE, 0, <Date>) realmente significa?

Também implementei o mesmo esquema de rotação de logs no meu ambiente de produção e adoraria vê-lo mais usado! (grande fã de Thomas Kejser, pena que o site dele só esteja disponível no arquivo da web).

O problema que você está enfrentando é devido a como o SQL lida com o 0 quando usado como partes de datediff, ele não converte para o tipo esperado. Se você fizer uma conversão explícita para datetime (não pode fazer datetime2, a propósito), você obtém o resultado esperado. Eu também sugeriria uma conversão explícita do tipo table para o tipo desejado, mas isso provavelmente não está impactando sua solução real.

SELECT v.dt
    , DATEDIFF(DAY, 0, v.dt) AS UsingZero
    , DATEDIFF(DAY, CONVERT(DATETIME, 0), v.dt) AS ConvertToDateTime
    , DATEDIFF(DAY, CONVERT(DATETIME, 0), CONVERT(DATETIME2(3), v.dt))
FROM (
    VALUES
        ('2024-08-17 23:59:59.997')
        , ('2024-08-17 23:59:59.998')
        , ('2024-08-17 23:59:59.999')
        , ('2024-08-18 00:00:00.000')
    )
    AS v(dt)