主页 / dba / 问题 / 341943
Accepted
Mark Storey-Smith
Asked: 2024-08-27 22:21:13 +0800 CST

DATEDIFF 舍入

  • 772

实施旋转分区方案，请参阅kejser.org/table-pattern-rotating-log-ring-。遇到 DATEDIFF 四舍五入值的问题：

DECLARE @Partitions INT = 15;

SELECT
    a1.dt
    , dtTrunc 
    , dtDiff
    , PartitionKey = CAST(DATEDIFF(DAY, 0, dtDiff) % @Partitions AS TINYINT)
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)
CROSS APPLY
    (
    SELECT
        dt = CAST(v.dt AS DATETIME2(3))
    ) a1
CROSS APPLY
    (
    SELECT
        dtTrunc = CAST(a1.dt AS DATE)
        , dtDiff = DATEDIFF(day, 0, a1.dt)
    ) a2

查询 1 输出

迄今为止已解决的问题：

DECLARE @Partitions INT = 15;

SELECT
    a1.dt
    , dtTrunc 
    , dtDiff
    , PartitionKey = CAST(DATEDIFF(DAY, 0, dtDiff) % @Partitions AS TINYINT)
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)
CROSS APPLY
    (
    SELECT
        dt = CAST(v.dt AS DATETIME2(3))
    ) a1
CROSS APPLY
    (
    SELECT
        dtTrunc = CAST(a1.dt AS DATE)
        , dtDiff = DATEDIFF(day, 0, CAST(a1.dt AS DATE))
    ) a2

查询 2 输出

这是预期/记录的行为吗？如果是，在哪里？

sql-server

2 个回答

  1. 这是预期/记录的行为吗？

    只有当您熟悉规则时才会这样，但是这些规则从未被完整记录下来。

    对于旧式datetime和smalldatetime类型来说尤其如此，它们保留了许多怪癖以实现向后兼容性。较现代的类型datetime2不允许将数字添加到日期或隐式转换整数等怪癖。许多怪癖都是过去可疑决策的结果。

    如果你想避免意外，请始终明确说明数据类型。

    原始代码中类型混乱的情况非常普遍。字符串不是日期/时间类型，而是字符串。CAST不能接受样式参数（与 不同CONVERT），这可能会导致歧义、不确定的结果，甚至运行时错误。DATEDIFF混合使用多种数据类型只会自找麻烦。

    例子

    我们以Jonathan Fite 的例子为例，因为它比原来更简单：

    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);

    该子句中的项目VALUES是字符串。在人类看来，它们可能看起来像日期和时间，但实际上并非如此。

    子句中的表达式SELECT受制于实现所执行的数据类型转换的神秘规则DATEDIFF（是的，有几个）。

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

    这里我们DATEDIFF用一个整数和一个varchar字符串作为参数进行调用。

    SQL Server 将零和dt字符串都转换为datetime，正如您在执行计划中所看到的：

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

    Union1004varchar是子句中为字符串指定的标签VALUES。文字 '1900-01-01 00:00:00.000' 是将零转换为 的常量折叠结果datetime。请注意，显示的字符串的格式是datetime特定的（它有三个小数部分）。

    字符串的隐式转换datetime是解释舍入问题的重要部分：

    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);
    日期 日期时间
    2024-08-17 23：59：59.997 2024-08-17 23：59：59.997
    2024-08-17 23：59：59.998 2024-08-17 23：59：59.997
    2024-08-17 23：59：59.999 2024-08-18 00:00:00.000
    2024-08-18 00:00:00.000 2024-08-18 00:00:00.000

    datetime注意由于精度有限（1/300 秒）而进行的四舍五入。

    导致舍入的不是零，而是字符串到的隐式转换datetime。

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

    现在我们已经DATEDIFF提供了一个datetime值和一个varchar字符串。

    SQL Server 将两者都转换为datetimeoffset，正如您在执行计划中所看到的：

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

    文字 '1900-01-01 00:00:00.000 +00:00' 是将零转换为 的常量折叠结果datetimeoffset(3)。显示的字符串格式具有时区偏移量和三位小数。您的字符串被隐式转换为datetimeoffset(7)。

    在这种情况下不会发生舍入，因为零映射到精确的日期值并且所有字符串都可以精确表示。

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

    最后，我们DATEDIFF提供了一个将字符串datetime转换为的结果（采用默认样式）。varchardatetime2(3)

    执行计划显示显式转换为datetime常量折叠为，datetimeoffset(3)并显示预期的三位小数和时区偏移量。您的varchar字符串首先datetime2(3)根据代码的要求显式转换为，然后隐式转换为datettimeoffset(3)：

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

    这可能并不是我们的本意。

    明确

    您没有说源值的数据类型是什么，但考虑到大多数计算的内部DATEDIFF用途datetimeoffset，我们不妨选择：

    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);

    结果：

    日期 类型
    2024-08-17 23:59:59.997 +00:00 45519
    2024-08-17 23:59:59.998 +00:00 45519
    2024-08-17 23:59:59.999 +00:00 45519
    2024-08-18 00:00:00.000 +00:00 45520

    执行计划显示：

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

    虽然不太容易看出来，但是这些[Union1004]值也有正确的类型：

    ('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')

    您可以选择或任何与源数据类型完全匹配的内容，但是计划中datetime2(3)会出现隐式转换，因为在这种情况下这是内部使用的。datetimeoffsetDATEDIFF

    建议

    为字符串日期/时间转换选择适当的确定性样式CONVERT，优先考虑CAST（不接受样式），如果您不喜欢令人困惑的结果和难以调试的情况，请明确说明您的数据类型。

    • 15
  2. Best Answer

    我在这里问了一个相关问题DATEDIFF(MINUTE, 0, <Date>) 中的 0 实际上是什么意思？

    我也在生产环境中实现了相同的日志轮换方案，很高兴看到它得到更多的应用！（Thomas Kejser 的忠实粉丝，可惜他的网站仅在网络档案中提供）。

    您遇到的问题是由于 SQL 在将 0 用作 datediff 的一部分时如何处理它，它不会转换为预期的类型。如果您显式转换为 datetime（顺便说一句，不能执行 datetime2），则会得到预期的结果。我还建议将类型表显式转换为所需类型，但这可能不会影响您的实际解决方案。

    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)
    • 6

相关问题