SQL Server DateDiff Vs .Net DateDiff

Question

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

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?

Thanks in advance

Answer 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

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

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

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

The value returned will be 1.9999421 instead of 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