SQL Server DateDiff和。net DateDiff

SQL Server:

SQL服务器:

DECLARE @Date1 DATETIME
SET @Date1 = '2012-26-12 12:00:05 AM'

DECLARE @Date2 DATETIME
SET @Date2 = '2012-28-12 12:00:00 AM'

SELECT @Date1, @Date2, DATEDIFF(DAY,@Date1,@Date2) 

return 2

VB.Net

DateDiff(DateInterval.Day,Convert.ToDateTime("26-12-2012 12:00:05 AM"), 
Convert.ToDateTime("28-12-2012 12:00:00 AM"))

return 1

Why are the results different? My two cents is that SQL Server's DateDiff subtracts only day part of the date time value (28 - 26) whereas .Net DateDiff precisely subtracts two date time values as seconds or milliseconds and converts into equivalent day value.

为什么结果不同?我的看法是，SQL Server的DateDiff只减去日期时间值的一部分(28 - 26)，而. net DateDiff精确地将两个日期时间值减去为秒或毫秒，并将其转换为等效的日值。

Or, is that because of operating system's regional and language settings?

还是因为操作系统的区域性和语言设置?

Thanks in advance

谢谢提前

2 个解决方案

#1

Actually the VB.NET expression is returning you the correct values since the total number of hours is not equal to 48 .Hence it is returning 1

VB。NET表达式返回的是正确的值，因为总小时数不等于48，因此返回的是1

In SQL Server , DateDiff function returns a round off value .

在SQL Server中，DateDiff函数返回一个舍入值。

Try dividing the value with total number of seconds in day which is 86400.0

尝试用一天中的总秒数除以这个值，即86400.0

SELECT @Date1, @Date2, DATEDIFF(ss,@Date1,@Date2) /86400.0

The value returned will be 1.9999421 instead of 2

返回的值将是1.9999421，而不是2

The difference between the 2 date in seconds is 172795 which is less than 172800 (Total seconds for 2 days).Hence the function should be returning only 1 if your not rounding off the result

两个日期的秒数差是172795，小于172800(2天总秒)。因此，如果不舍入结果，函数应该只返回1

#2

Day DateDiff round down the number returned. Try to return hours and divide by 24 to get count of days

Day DateDiff将返回的数字四舍五入。试着返回小时，除以24，得到天数

DateDiff(DateInterval.Hour,Convert.ToDateTime("26-12-2012 12:00:05 AM"), 
Convert.ToDateTime("28-12-2012 12:00:00 AM"))/24

#1