摘要:SQL Server - DATEDIFF 函數造成溢位。分隔兩個日期/時間執行個體的日期部分數目太大。請嘗試使用日期部分較不精確的 DATEDIFF。
因為 兩個日期之間差距了100年。
而導致用秒去算的時後發生了錯誤。
select DATEDIFF (second, StartDate, SysDateTime()) from TableName
為了解決這個問題從網路上找到了解答，如下
create function fn_diffsecond
(
@date1 datetime,
@date2 datetime
)
returns bigint
as
begin
return (convert(bigint, datediff(day, @date1, @date2)) * 24 * 60 * 60)
- (datediff(second, dateadd(day, datediff(day, 0, @date1), 0), @date1))
+ (datediff(second, dateadd(day, datediff(day, 0, @date2), 0), @date2))
end
go
select dbo.fn_diffsecond('1900-01-02 03:45:56', '9999-12-31 23:59:59')
select
(convert(bigint, datediff(day, [StartDate] , SysDateTime())) * 24 * 60 * 60)
- (datediff(second, dateadd(day, datediff(day, 0, [StartDate]), 0), [StartDate]))
+ (datediff(second, dateadd(day, datediff(day, 0, SysDateTime()), 0), SysDateTime()))
from TableName