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

Answer 1

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

Answer 2

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

Answer 3

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