CODEWITHSUNDEEP
c#.netlinqdatetime
NoWar
NoWar

Reputation: 37633

How to compare DateTime without time via LINQ?

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!

Upvotes: 84

Views: 159392

Answers (9)

Jon Skeet
Jon Skeet

Reputation: 1499760

Just use DateTime.Today property to take the current date. There's no need to truncate t.StartDate in this case (and doing so may incur performance penalties).

var today = DateTime.Today;

var q = db.Games.Where(t => t.StartDate >= 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.

Upvotes: 92

Tomáš Florek
Tomáš Florek

Reputation: 21

The .Date answer is misleading since you get the error mentioned before. Another way to compare, other than mentioned DbFunctions.TruncateTime, may also be:

DateTime today = DateTime.Now.date;
var q = db.Games.Where(t => SqlFunctions.DateDiff("dayofyear", today, t.StartDate) <= 0
      && SqlFunctions.DateDiff("year", today, t.StartDate) <= 0)

It looks better(more readable) in the generated SQL query. But I admit it looks worse in the C# code XD. I was testing something and it seemed like TruncateTime was not working for me unfortunately the fault was between keyboard and chair, but in the meantime I found this alternative.

Upvotes: 2

Sushmit Saxena
Sushmit Saxena

Reputation: 67

I found this question while I was stuck with the same query. I finally found it without using DbFunctions. Try this:

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

This way by bifurcating the date parts we effectively compare only the dates, thus leaving out the time.

Hope that helps. Pardon me for the formatting in the answer, this is my first answer.

Upvotes: 0

Duane Theriot
Duane Theriot

Reputation: 2185

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

Upvotes: 135

Joao Arruda
Joao Arruda

Reputation: 223

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.

Upvotes: 10

&#199;ağdaş Karademir
&#199;ağdaş Karademir

Reputation: 756

Try this code

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

Upvotes: 20

Ramanand Potdar
Ramanand Potdar

Reputation: 31

DateTime dt=DateTime.Now.date;

var q = db.Games.Where(
    t =>EntityFunction.TruncateTime(t.StartDate.Date >=EntityFunction.TruncateTime(dt)).OrderBy(d => d.StartDate
);

Upvotes: 0

Caesar
Caesar

Reputation: 480

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

Upvotes: 12

Matt Burland
Matt Burland

Reputation: 45135

Try

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

Upvotes: 0

Related Questions