How to compare DateTime without time via LINQ?

Question

I have

var q = db.Games.Where(t => t.StartDate >= DateTime.Now).OrderBy(d => d.StartDate);

But it compares including time part of DateTime. I really don't need it.

How to do it without time?

Thank you!

Solution 1

Just use the Date property:

var today = DateTime.Today;

var q = db.Games.Where(t => t.StartDate.Date >= today)
                .OrderBy(t => t.StartDate);

Note that I've explicitly evaluated DateTime.Today once so that the query is consistent - otherwise each time the query is executed, and even within the execution, Today could change, so you'd get inconsistent results. For example, suppose you had data of:

Entry 1: March 8th, 8am
Entry 2: March 10th, 10pm
Entry 3: March 8th, 5am
Entry 4: March 9th, 8pm

Surely either both entries 1 and 3 should be in the results, or neither of them should... but if you evaluate DateTime.Today and it changes to March 9th after it's performed the first two checks, you could end up with entries 1, 2, 4.

Of course, using DateTime.Today assumes you're interested in the date in the local time zone. That may not be appropriate, and you should make absolutely sure you know what you mean. You may want to use DateTime.UtcNow.Date instead, for example. Unfortunately, DateTime is a slippery beast...

EDIT: You may also want to get rid of the calls to DateTime static properties altogether - they make the code hard to unit test. In Noda Time we have an interface specifically for this purpose (IClock) which we'd expect to be injected appropriately. There's a "system time" implementation for production and a "stub" implementation for testing, or you can implement it yourself.

You can use the same idea without using Noda Time, of course. To unit test this particular piece of code you may want to pass the date in, but you'll be getting it from somewhere - and injecting a clock means you can test all the code.

Solution 2

The Date property is not supported by LINQ to Entities -- you'll get an error if you try to use it on a DateTime field in a LINQ to Entities query. You can, however, trim dates using the DbFunctions.TruncateTime method.

var today = DateTime.Today;
var q = db.Games.Where(t => DbFunctions.TruncateTime(t.StartDate) >= today);

Solution 3

Try this code

var today = DateTime.Today;
var q = db.Games.Where(t => DbFunctions.TruncateTime(t.StartDate) <= today);

Solution 4

I found that in my case this is the only way working: (in my application I want to remove old log entries)

 var filterDate = dtRemoveLogs.SelectedDate.Value.Date;
 var loadOp = context.Load<ApplicationLog>(context.GetApplicationLogsQuery()
              .Where(l => l.DateTime.Year <= filterDate.Year
                       && l.DateTime.Month <= filterDate.Month
                       && l.DateTime.Day <= filterDate.Day));

I don't understand why the Jon's solution is not working ....

Solution 5

It happens that LINQ doesn't like properties such as DateTime.Date. It just can't convert to SQL queries. So I figured out a way of comparing dates using Jon's answer, but without that naughty DateTime.Date. Something like this:

var q = db.Games.Where(t => t.StartDate.CompareTo(DateTime.Today) >= 0).OrderBy(d => d.StartDate);

This way, we're comparing a full database DateTime, with all that date and time stuff, like 2015-03-04 11:49:45.000 or something like this, with a DateTime that represents the actual first millisecond of that day, like 2015-03-04 00:00:00.0000.

Any DateTime we compare to that DateTime.Today will return us safely if that date is later or the same. Unless you want to compare literally the same day, in which case I think you should go for Caesar's answer.

The method DateTime.CompareTo() is just fancy Object-Oriented stuff. It returns -1 if the parameter is earlier than the DateTime you referenced, 0 if it is LITERALLY EQUAL (with all that timey stuff) and 1 if it is later.

View more solutions

Comments

[Jon Skeet]

See my answer for why that's not a good idea; also, DateTime.Today is a simpler approach.

[Matt Burland]

@JonSkeet: Good point. Didn't catch that. That's exactly one of those bugs that would never happen during the development (because you're probably testing during the day) and instead will come back and haunt you later!

[Alexei Levenkov]

+1. "may need to use Utc" is good point. In addition to caching value of "now" it would be better to pass it as argument to a function so you can unit test it (testing code with DateTime.Today/DateTime.Now is not easy).

[Jon Skeet]

@AlexeiLevenkov: Or inject a clock, yes. Will add that.

[Lance U. Matthews]

Why are you comparing the Month property of one DateTime to the Year property of the other?

[Magnus Johansson]

Just a note, EntityFunctions has now been replaced by DbFunctions in later versions of the Entity Framework

[ihebiheb]

I'm the only one that your solution and the solution of Jon raise me the error "The specified type member 'Date' is not supported in LINQ to Entities" ?

[SAR]

The specified type member 'Date' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported.

[Jon Skeet]

@SAR: Indeed - I'd expect to see something in SqlFunctions for this, but I can't see it...

[SAR]

@JonSkeet Actully i want to compare to date(now and given date) with out time in MVC5 i could not fine any solution in my case or i count not understand the solutions to implement, any idia in linq to compare only tow date with out time , thanks

[Jon Skeet]

@SAR: The MVC part is irrelevant, and "in LINQ" is too vague: you specifically mean "in Entity Framework". A quick search found stackoverflow.com/questions/13539224 - it sounds like you want EntityFunctions.TruncateTime.

[Karl Wenzel]

After seeing this for the first time, it looks like System.Data.Entity.DbFunctions has a number of useful other methods as well. Very helpful!

[Yeshwant Mudholkar]

Awesome solution. Worked for me in implementing date column comparing in Web API!!! Surprisingly after 5 years also it helped me that is more great.

[George Birbilis]

Pronlem is when you have same column involved in more than one filtering predicates you'll end up with one being picked as seek predicate and the rest become residual predicates

[Blasco73]

Great... smart and simply idea

[Leandro Bardelli]

Don't use dynamic or "var" if you don't need it.