Day Date difference between two date calculations in SQL AND C # producing a different result

Question

I calculate the difference per day on two dates. In c #

diffdays = (EndDate-StartDate).Days 

therefore, considering Enddate as 6/26/2015 and starting on 6/10/2015, the diffdays value is 15, as shown in the Autos section during debugging.

While on the SQL server, what am I doing

 SELECT DATEDIFF(day, StartDate, EndDate ) 

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

I need these two days to be the same. What am I doing wrong?

Answer 1

The TimeSpan.Days property returns only whole days, discarding any fractional part. Depending on the time portion of your two DateTime , you may expect the behavior you see.

Try to derive the time part from the equation using the Date property (and effectively set both times before midnight):

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

Alternatively, you can round the TotalDays property (including fractional parts of days):

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

Answer 2

DATEDIFF SQL function counts the number of times you pass the border specified as units, whereas the .NET DateTime.Subtract() function (you use this function if you implicitly use the minus operator) returns the actual TimeSpan between two dates, so you should 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') 

The difference between the two dates is only 4 minutes, but since the border between the two dates (at 12:00 midnight) has passed, it returns 1. The same two dates will return a TimeSpan of 4 minutes in C #. If you check only the Days (not TotalDays ) part of this TimeSpan (as you do above), you will get 0.

Answer 3

In C #, you calculate the number of days between two dates. In SQL, the DATEDIFF function is a calculation of the number of days after the first date, including the end date.

To fix this, the easiest way is to add 1 to the C # diffdays .

Answer 4

Your C # code returns a strange result ... I created a simple console application and a variable other than 16, as in SQL Server

  DateTime startDate = new DateTime(2015, 6, 10); DateTime endDate = new DateTime(2015, 6, 26); var differ = (endDate - startDate).Days; 

Please double check C # results