DbFunctions DiffDays Gives wrong answer

Question

I want to get a list from DataBase, where MyDate is today or tomarrow.
I wrote the following code.

_Log("Now: " + DateTime.Now.ToString());

var v = db_TS.TS_Test.Where(x => DbFunctions.DiffDays(x.MyDate,DateTime.Now) < 2);
foreach (var item in v.ToList())
{
     _Log("MyDate: " + item.MyDate.ToString());
}

The following is logged:

Now: 11/08/2016 10:50:00
MyDate: 27/09/2017 09:35:00

Please help me to find what went wrong in the code?
Thank you

score 18 · Accepted Answer · answered Oct 10 '18 at 21:03

You should be doing DbFunctions.DiffDays(DateTime.Now,x.MyDate) since it's supposed to work like subtracting the first parameter from the second one, so in your case, the DiffDays is returning a negative number.

Summarizing it if you have DbFunctions.DiffDays(date1,date2)

and date1 > date2 the result will be < 0

and date1 < date2 the result will be > 0

PiotrWolkowski · Answer 2 · 2016-08-11T03:31:15.530

0

Simplest approach is to check year, month, and day with the current date. Month and year should be the same. Day has to be the same for today and one day less for tomorrow:

var v = db_TS.TS_Test.Where(x => x.MyDate.Year == DateTime.Now.Year &&
                                 x.MyDate.Month == DateTime.Now.Month && 
                                 x.MyDate.Day >= DateTime.Now.Day - 1);

Edit

As pointed out this simplistic scenario won't work for edge cases.

Better option is to subtract a date from today and check what's the result in days. If it's tomorrow then the difference is 1 day or 0 if it's today.

var v = db_TS.TS_Test.Where(x => DateTime.Now.Subtract(x.MyDate).Days <= 1);

edited Aug 11 '16 at 03:31

answered Aug 11 '16 at 03:23

PiotrWolkowski

8,408
6
48
68

I think this will fail at the year boundary. – Adam V Aug 11 '16 at 03:27
@AdamV You are absolutely right. I added more robust solution. Left the old one in case someone would like to use the idea and improve on that. – PiotrWolkowski Aug 11 '16 at 03:32

score 0 · Answer 3 · edited Jan 18 '21 at 12:32

0

please check here from more info of datediff function.

Syntax

-- DATEDIFF ( datepart , startdate , enddate )

-- Example usage

  SELECT DATEDIFF(DAY, GETDATE(), GETDATE() + 1) AS DayDiff
  SELECT DATEDIFF(MINUTE, GETDATE(), GETDATE() + 1) AS MinuteDiff
  SELECT DATEDIFF(SECOND, GETDATE(), GETDATE() + 1) AS SecondDiff
  SELECT DATEDIFF(WEEK, GETDATE(), GETDATE() + 1) AS WeekDiff
  SELECT DATEDIFF(HOUR, GETDATE(), GETDATE() + 1) AS HourDiff

You can play with here

edited Jan 18 '21 at 12:32

Community

1
1

answered Aug 11 '16 at 05:58

KARAN

1,023
1
12
24

1

This person was asking about DbFunctions.DiffDays in EF, not directly in SQL (https://learn.microsoft.com/en-us/dotnet/api/system.data.objects.entityfunctions.diffdays?redirectedfrom=MSDN&view=netframework-4.7.2#System_Data_Objects_EntityFunctions_DiffDays_System_Nullable_System_DateTime__System_Nullable_System_DateTime__) – Sylvain Gantois Aug 18 '18 at 06:33

DbFunctions DiffDays Gives wrong answer

3 Answers3