Day Date difference between two date calculation in SQL AND C# producing varying result

Question

I am calculating the day difference in two dates. In C#

diffdays = (EndDate-StartDate).Days

so considering the Enddate as 6/26/2015 and startdate as 6/10/2015 the diffdays value is 15 as shown in the Autos section while debugging.

While in SQL server what I am doing is

SELECT DATEDIFF(day, StartDate, EndDate )

where EndDate is 6/26/2015 and startdate is 6/10/2015 and it gives a result 16.

I need these two day difference to be same. What is it that I am doing wrong?

Answer 1

The TimeSpan.Days property returns whole days only, dropping any fractional portion. Depending on the time portion of your two DateTime's, you could expect the behavior you're seeing.

Try taking the time portion out of the equation by using the Date property (and effectively setting both times to midnight):

diffdays = (EndDate.Date - StartDate.Date).Days

Alternatively, you can round up the TotalDays property (which includes fractional portions of days):

diffdays = Math.Ceiling((EndDate - StartDate).TotalDays);

Answer 2

DATEDIFF function of SQL counts the number of times you pass the boundary specified as units, whereas .NET's DateTime.Subtract() function (you use this function when implicitly through the use of minus operator) returns the actual TimeSpan between the two dates, so you are bound to see differences between the two results.

EXAMPLE

The following query will return 1:

SELECT DATEDIFF(day, '1/1/2015 23:58:00', '1/2/2015 00:02:00')

There is a difference of only 4 minutes between the two dates, but since a day boundary has passed between the two dates (at 12:00 midnight), it returns 1. The same two dates will return a TimeSpan of 4 minutes in C#. If you check just the Days part (not TotalDays) of that TimeSpan object (as you're doing above), you'll get 0.