DATEDIFF溢出

Question

我在中使用以下代码

SELECT cast(DATEDIFF(ms,cast(Start as datetime2),cast(EndTime as datetime2)
                        ) as float) AS [total]--difference to be calculated in millisecond
        FROM systable

但是遇到一个错误，因为“datediff函数导致溢出。分隔两个日期/时间实例的日期部分的数量太大了。请尝试将datediff与不太精确的datepart一起使用。”

我的要求是有不同的毫秒，如果这改变了，那么它将影响其他结果。

请你提供一些帮助

Answer 1

这是因为DATEDIFF()函数返回一个整数。整数只允许值高达2,147,483,647。在这种情况下，导致数据类型溢出的值超过了2B。理想情况下，您应该使用大()函数，它返回一个比金，该函数允许的值最多为9,223,372,036,854,775,807或~9七十七千。DATEDIFF_BIG()在/中不受支持(截止到2020年1月)。

您可以在这里投票支持该功能：(https://feedback.azure.com/forums/307516/suggestions/14781627)

通过测试DATEDIFF()，您可以看到，在没有整数之前，您可以得到25天和20个小时的日期差。下面是一些示例代码。

DECLARE @startdate DATETIME2 = '01/01/2020 00:00:00.0000';
DECLARE @enddate DATETIME2   = '01/01/2020 00:00:02.0000';

-- Support:
--   MILLISECOND:    ~25 days 20 Hours
--   MICROSECOND:    ~35 minutes
--   NANOSECOND:     ~ 2 seconds

SELECT
    DATEDIFF(DAY, @startdate, @enddate)             [day]
    , DATEDIFF(HOUR, @startdate, @enddate)          [hour]
    , DATEDIFF(MINUTE, @startdate, @enddate)        [minute]
    , DATEDIFF(SECOND, @startdate, @enddate)        [second]
    , DATEDIFF(MILLISECOND, @startdate, @enddate)   [millisecond]
    , DATEDIFF(MICROSECOND, @startdate, @enddate)   [microsecond]
    , DATEDIFF(NANOSECOND, @startdate, @enddate)    [nanosecond]

在此期间，您可以计算每个值从时间开始以来的滴答数，然后减去差额。对于一个DATETIME2，可以像这样计算蜱数：

CREATE FUNCTION dbo.DATEDIFF_TICKS(@date DATETIME2)
RETURNS BIGINT
AS
BEGIN

    RETURN
        (DATEDIFF(DAY, '01/01/0001', CAST(@date AS DATE)) * 864000000000.0)
        + (DATEDIFF(SECOND, '00:00', CAST(@date AS TIME(7))) * 10000000.0)
        + (DATEPART(NANOSECOND, @date) / 100.0);
END
GO

然后，您只需运行该函数并确定滴答和滴答之间的差异。

DECLARE @startdate DATETIME2 = '01/01/2020 00:00:00.0000';
DECLARE @enddate DATETIME2 = '01/30/2020 00:00:00.0000';

SELECT
    dbo.DATEDIFF_TICKS(@startdate)      [start_ticks],
    dbo.DATEDIFF_TICKS(@startdate)      [end_ticks],
    dbo.DATEDIFF_TICKS(@enddate) - dbo.DATEDIFF_TICKS(@startdate) [diff];

下面是一个运行500年差异的示例：

DECLARE @startdate DATETIME2 = '01/01/2000 00:00:00.0000';
DECLARE @enddate DATETIME2 = '01/01/2500 00:00:00.0000';

SELECT
    dbo.DATEDIFF_TICKS(@startdate)      [start_ticks],
    dbo.DATEDIFF_TICKS(@startdate)      [end_ticks],

    dbo.DATEDIFF_TICKS(@enddate) - dbo.DATEDIFF_TICKS(@startdate) [diff];

结果：

start_ticks          end_ticks            diff
-------------------- -------------------- --------------------
630822816000000000   630822816000000000   157785408000000000