时间戳

首页 > 解决方案 > datediff 函数导致溢出消息

sql-server - datediff 函数导致溢出消息

问题描述

做这样的事情：

select * 
from INVOICE_HEADING 
where INVOICE_DATE >=  '06 Dec 2018 00:00:00'
INVOICE_DATE <= '16 Dec 2018 00:00:00'

我收到这条消息：

“datediff 函数导致溢出。分隔两个日期/时间实例的 dateparts 数量太大。请尝试将 datediff 与不太精确的 datepart 一起使用。”

如何以不同的方式编写它（或者我如何在这里使用 datediff？）来获得结果？

标签： sql-server

解决方案

函数DATEDIFF返回一个有符号整数，它可以保存从 -2.147.483.648 到 2.147.483.647 的值。如果您应用函数的日期和您使用的单位（月、日、秒等）在这些范围之外产生差异，则会引发错误。

有一些解决方法：

  • 如果您使用的是 SQL Server 2016+，请使用DATEDIFF_BIG 。
  • 移动到“更高”的单位（毫秒 -> 秒 -> 分钟 -> 小时等），直到您获得的值可以转换为整数，并确保您将来可能应用该函数的所有值都将仍然在整数的范围内。BIGINT然后，您可以通过将值相乘并将其处理为（例如），将单位向下钻取到您需要的单位。
  • 比较对业务无效或默认生成为1900-01-01. 您可以使用子句过滤它们WHERE，提供一个不错的默认值或转换为NULL. 当日期不合理时，也可以避免在它之前应用DATEDIFF函数。CASE

例子：

DECLARE @OldDate DATE = '1900-01-01'

DECLARE @Now DATE = GETDATE()

SELECT DATEDIFF(SECOND, @OldDate, @Now) AS DateDiffResult

--Msg 535, Level 16, State 0, Line 5
--The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.

将单位从秒更改为分钟：

DECLARE @OldDate DATE = '1900-01-01'

DECLARE @Now DATE = GETDATE()

SELECT DATEDIFF(MINUTE, @OldDate, @Now) AS DateDiffResult

-- DateDiffResult: 62599680

使用“更大”的数据类型将分钟恢复为秒：

DECLARE @OldDate DATE = '1900-01-01'

DECLARE @Now DATE = GETDATE()

SELECT 
    CONVERT(BIGINT, DATEDIFF(MINUTE, @OldDate, @Now)) * 60 AS DateDiffResult

-- DateDiffResult: 3755980800

推荐阅读