SQL Server DateDiff Vs .Net DateDiff
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
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
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