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??

Are both using the same time zone? Perhaps try DateTime.UtcNow instead? — Mike Christensen, Sep 30 '11 at 15:09

Conrad Frix · Answer 1 · 2011-09-30T16:38:40.973

5

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

edited Sep 30 '11 at 16:38

answered Sep 30 '11 at 15:11

Conrad Frix

51,984
12
96
155

int differenceInDays = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day).Subtract(new DateTime(otherDate.Year, otherDate.Month, otherDate.Day)); – daniloquio Sep 30 '11 at 15:29
Doesn't seem to work, because the date I am subtracting already has the time stripped out (i.e., its 10/1/2011 00:00:00), so it is returning the same (0), while the sql datediff is returning 1 – M.R. Sep 30 '11 at 16:27
`DateTime.Now()` doesn't have the time stripped out (except for one case) – Conrad Frix Sep 30 '11 at 16:28
Right, but given the same datetime in both sql and c# they should return the same value, but didn't seem to - unless I did what @itsmat said, which is subtract the .Date - it was helpful anyway, so +1! – M.R. Sep 30 '11 at 20:43
I won't argue that the itsmatt and Stealth Rabbi's answers aren't cleaner and Accepting either is fine with me. But my answer does indeed arrive at the same answer. Perhaps you missed the `.Subtract(CurrentDate.TimeOfDay)` part. – Conrad Frix Sep 30 '11 at 20:50

itsmatt · Accepted Answer · 2011-09-30T15:30:22.660

4

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.

edited Sep 30 '11 at 15:30

answered Sep 30 '11 at 15:16

itsmatt

31,265
10
100
164

this works... the key I think is subtracting the 'date', and not just the datetime var... – M.R. Sep 30 '11 at 16:29

score 3 · Answer 3 · answered Sep 30 '11 at 15:16

I'm assuming you want the number of Total days, not the number of days from the largest previous unit. You'd want to use the TotalDays property. Also, you may find it easier to use the minus operator to do a subtraction

DateTime d1 = DateTime.Now;
DateTime d2 = new DateTime(2009, 1, 2);

TimeSpan difference = d1 - d2;
Console.WriteLine(difference.TotalDays); // Outputs (today):1001.46817997424

Exact c# result of sql datediff

3 Answers3