sql-server – SQL Server：datediff函数导致溢出

发布时间：2020-12-12 06:46:37 所属栏目：MsSql教程来源：网络整理

导读：这个错误意味着什么,我该如何避免呢？ 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. 我没有使用datediff函数.我正在进行

这个错误意味着什么,我该如何避免呢？

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.

我没有使用datediff函数.我正在进行此查询,其中Timestamp是日期时间类型：

SELECT TOP 10 * from vSomeView 
WHERE TimestampUTC >= '2009-08-13 22:17:00'

我能做错什么？

我正在使用SQL Server 2008.

解决方法

SQL Server可能在内部进行DATEDIFF进行比较,如果两个日期间隔超过68年(并且内部DATEDIFF是秒),DATEDIFF可能会出错,因为DATEDIFF的输出是INT.

我之前碰到过这个(直接使用DATEDIFF)并通过将DATETIME转换为DECIMAL来解决,如下所示：

DECLARE @d1 DATETIME
DECLARE @d2 DATETIME

DECLARE @n1 AS DECIMAL(38,20)
DECLARE @n2 AS DECIMAL(38,20)

SET @d1 = '2 Jan 2000 00:00:02'
SET @d2 = '1 Jan 2000 00:00:00'

-- @n1 and @n2 will hold the datetime in fractional form. The integer part
-- is the #days since 1 Jan 1900,whilst the fractional part is the time in
-- 1/86400's of a second (24 hours = 86400 seconds,so a fraction of 0.5
-- represents 12:00:00 noon precisely.
SELECT @n1 = CAST(@d1 AS DECIMAL(38,20)),@n2 = CAST(@d2 AS DECIMAL(38,20))

-- Now manipulate the fractional and integer parts of the time
-- to get the final seconds difference.
SELECT CAST(86400 AS DECIMAL(38,20)) * (@n1 - @n2)

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!