SQL Server DateDiff Vs .Net DateDiff

SQL Server:

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

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

Need Your Help

OpenGL First person camera position issues

java opengl lwjgl

I have a "camera" in my opengl program that I recently finished. However, I've noticed that whenever I rotate and then move again, the x, y, and z angles change. For example, when I press the "w" k...

magento REST API not accessible in iphone

iphone magento oauth magento-1.7

When I try to access rest API using iPhone I have passed following parameter in URL and used both methods POST and GET but it displays Access Denied.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.