SQL Server DateDiff与.Net DateDiff

问题描述：

SQL Server:

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

为什么结果不同?我的两分钱是，SQL Server的 DateDiff 仅减去日期时间值的一天部分(28-26)，而.Net DateDiff 精确地减去两个日期时间值，以秒或毫秒为单位并转换为等效的日值.

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.

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

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

预先感谢

答

实际上，由于 hours 的总数不等于 48，因此VB.NET表达式会向您返回正确的值.因此它返回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

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

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

请尝试将该值除以一天中的秒总数，即 86400.0

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

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

返回的值将是 1.9999421 而不是 2

两个日期之间的时差(以秒为单位)是 172795 ，小于 172800 (两天的总秒数).因此，如果您没有四舍五入的结果

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

SQL Server DateDiff与.Net DateDiff

相关推荐