这个错误意味着什么,我该如何避免呢？
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)
