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...
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!
9 Answers
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);
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.
Try this code
var today = DateTime.Today;
var q = db.Games.Where(t => DbFunctions.TruncateTime(t.StartDate) <= today);
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 ....
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.
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.
Try
var q = db.Games.Where(t => t.StartDate.Date >= DateTime.Now.Date).OrderBy(d => d.StartDate);
DateTime dt=DateTime.Now.date;
var q = db.Games.Where(
t =>EntityFunction.TruncateTime(t.StartDate.Date >=EntityFunction.TruncateTime(dt)).OrderBy(d => d.StartDate
);
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.
Chloe Bennett explores the intersection of pop culture, streaming entertainment, digital trends, and contemporary lifestyle. Her weekly commentary reaches thousands of culture enthusiasts.
