C# DateTime down to the minute from linq

Question

I'am trying to compare two DateTimes down the the minute from a DateTime.Now and a SQL entry. I see the problem being that the ticks are slightly off due to the kind being returned from the DB. However I can't figure out a work around.

This is my DateTime to grab

DateTime toGrab = DateTime.Now.AddMinutes(10)
                              .AddSeconds(-DateTime.Now.Second)     
                              .AddMilliseconds(-DateTime.Now.Millisecond);

And there is my linq expression.

cc.DBAppointments.Where(c => c.StartDate == toGrab && c.Approved == 1).ToList();

Any advise?

Answer 1

I'm going to assume you are using Entity Framework for this. If you just need to compare the datetime up until minutes, you can use System.Data.Entity.DbFunctions to access canonical functions in linq-to-entities. You can use this for example:

cc.DBAppointments
  .Where(c => DbFunctions.DiffMinutes(c.StartDate,DateTime.Now) == 0 && c.Approved == 1)
  .ToList();

This way you have access to advanced sql datetime comparison and you don't have to do tricks adding seconds/milliseconds to your DateTime.Now. This function will ignore everything smaller than minutes .

Answer 2

You're making multiple references to DateTime.Now, which possibly has a different value each time. The following should work better:

var now = DateTime.Now;
var toGrab = now.Date.AddHours(now.Hour).AddMinutes(now.Minute + 10);

Note that, in order to get a clear "top of the minute", adding hours and minutes to a date would work better than trying to remove the seconds and milliseconds because there's more detail in a DateTime than milliseconds.

Answer 3

Change

DateTime toGrab = DateTime.Now.AddMinutes(10)
                              .AddSeconds(-DateTime.Now.Second)
                              .AddMilliseconds(-DateTime.Now.Millisecond);

To

DateTime now = DateTime.Now;
DateTime toGrab = now.AddMinutes(10)
                     .AddSeconds(-now.Second)
                     .AddMilliseconds(-now.Millisecond);

On a different note, is there any reason why you would need to grab an appointment to the exact millisecond? It seems very prone to missing records?

Edit

Also, in your database, the value 2015-12-10 10:33:48.373 is different to 2015-12-10 10:33:48.374 even though they are 1 milisecond apart. If you want to get a broader range, you can do c.StartDate >= startDate && c.StartDate <= endDate && c.Approved == 1

Answer 4

You can do comparisons on TimeSpan objects, which means you can do it like this:

TimeSpan oneMinute = new TimeSpan(0, 1, 0);
cc.DBAppointments.Where(c =>
    DateTime.Now.AddMinutes(10).Subtract(c.StartDate) < oneMinute &&
    c.Approved == 1).ToList();

Of course if c.StartDate is less than 9 minutes in the future, the subtraction will have a result of <-1 minutes. I'm not sure if your objective is within 1 minute. If that's the case, you can add .Duration() to your equation, which will return the absolute value of the TimeSpan:

cc.DBAppointments.Where(c =>
    DateTime.Now.AddMinutes(10).Subtract(c.StartDate).Duration() < oneMinute &&
    c.Approved == 1).ToList();