Exact c# result of sql datediff

Question

I'm trying to get the number of days (calculated byu datediff) in sql and the number of days in c# (calculated by DateTime.now.Substract) to be the same, but they return different results....

//returns 0
int reso = DateTime.Now.Subtract(expirationDate).Days;

vs

//returns 1
dateDiff(dd,getDate(),ExpirationDate)

In both cases, ExpirationDate is '10/1/2011 00:00:00', and the code and the DB are sitting on the same server. I want the return int to be the same. I suspect I'm missing something stupid... ideas??

Answer 1

dateDiff(dd,getDate(),ExpirationDate) Is doing a days comparison. DateTime.Now.Subtract(expirationDate).Days is doing a date and time

For example

SELECT dateDiff(dd,'10/1/2011 23:59:00' , '10/2/2011') returns one day even when only one minute apart.

If you want the same in C# you need to remove the time component

e.g.

DateTime  dt1 = new DateTime(2011,10,1, 23,59,0);
DateTime  dt2 = new DateTime(2011,10,2, 0,0,0);

Console.WriteLine((int) dt2.Subtract(dt1.Subtract(dt1.TimeOfDay)));

So in your case it would be something like

DateTime CurrentDate = DateTime.Now;
int reso = CurrentDate.Subtract(CurrentDate.TimeOfDay).Subtract(DateTime.expirationDate).Days;

I haven't tested it but I would not do DateTime.Now.Subtract(DateTime.Now.Subtract.TimeOfDay)

Because the second call to Now wouldn't be guaranteeing to be the same as first call to Now

In any case Stealth Rabbi's answer seems more elegant anyway since you're looking for a TimeSpan not a DateTime

Answer 2

10/1/2011 is less than 1 day away from DateTime.Now. Since you're getting back a TimeSpan and then applying Days to it, you're getting back a TimeSpan that is < 1 day. So it'll return 0 Days.

Instead, just use the Date component of those DateTimes and it'll correctly report the number of days apart - like this:

DateTime now = DateTime.Now;
DateTime tomorrow = new DateTime(2011, 10, 1);
var val = (tomorrow.Date - now.Date).Days;

This will yield you 1 day.