CODEWITHSUNDEEP
c#sql-serverdatabase
wingskush
wingskush

Reputation: 554

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

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?

Upvotes: 9

Views: 3164

Answers (4)

dotNET
dotNET

Reputation: 35400

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.

Upvotes: 1

Grant Winney
Grant Winney

Reputation: 66439

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);

Upvotes: 7

Disappointed
Disappointed

Reputation: 1120

Your C# code returns strange result ... I created simple console application and variable differ equals 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 your C# results

Upvotes: 0

devjames
devjames

Reputation: 34

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

To fix this this simplest thing to do would be to add 1 to the C# diffdays variable.

Upvotes: -1

Related Questions