How to compare only date components from DateTime in EF?

Question

I am having two date values, one already stored in the database and the other selected by the user using DatePicker. The use case is to search for a particular date from the database.

The value previously entered in the database always has time component of 12:00:00, where as the date entered from picker has different time component.

I am interested in only the date components and would like to ignore the time component.

What are the ways to do this comparison in C#?

Also, how to do this in LINQ?

UPDATE: On LINQ to Entities, the following works fine.

e => DateTime.Compare(e.FirstDate.Value, SecondDate) >= 0

Newfashioned

You can also take a look at this SO question: https://mcmap.net/q/86288/-how-to-compare-only-date-without-time-in-datetime-types/… – Buehler

Answer 1

NOTE: at the time of writing this answer, the EF-relation was unclear (that was edited into the question after this was written). For correct approach with EF, check Mandeeps answer.

---

You can use the DateTime.Date property to perform a date-only comparison.

DateTime a = GetFirstDate();
DateTime b = GetSecondDate();

if (a.Date.Equals(b.Date))
{
    // the dates are equal
}

Flied

It's easy to compare date but the question is related to LINQ to Entities who is unable to convert .Date property into SQL. – Exclude

@MichaëlCarpentier: good point. Apparently it still solved the OP's problem. – Ainslee

This doesn't query the database but rather processes the data in the CLR / application layer after the fact. The real solution is to use the EntityFunctions.TruncateTime(..) function as specified in the answer below, since it sends the query to the database and allows the processing to be done at the storage layer. Without this you couldn't use the date comparison logic in Where / Count clauses and then further query on the filtered data, since you'd have to pull partial results into the application layer first, which can be a deal-breaker in scenarios that process large bodies of data. – Tragedienne

@Tragedienne Yes, EntityFunctions.TruncateTime certainly seem to be the way to go these days (it became available in .NET 4 which was released the year after this question was asked). – Ainslee

use System.Data.Entity.DbFunctions.TruncateTime() method. You need to add a reference to EntityFramework – Willed

Answer 2

Use the class EntityFunctions for trimming the time portion.

using System.Data.Objects;    

var bla = (from log in context.Contacts
           where EntityFunctions.TruncateTime(log.ModifiedDate) ==  EntityFunctions.TruncateTime(today.Date)
           select log).FirstOrDefault();

Source: http://social.msdn.microsoft.com/Forums/en-US/csharpgeneral/thread/84d4e18b-7545-419b-9826-53ff1a0e2a62/

UPDATE

As of EF 6.0 and later EntityFunctions is replaced by DbFunctions.

Berserk

Just a note EntityFunctions has been deprecated in favor of System.Data.Entity.DbFunctions for (at least) EF6. It may have been earlier than this. – Indigestion

I wouldn't be quick to jump to this solution as it is really slow, more info: #22777343 – Snug

Doesn't seem to work with a SQLite database. I get "SQL logic error or missing database no such function: TruncateTime". – Bold

Answer 3

I think this could help you.

I made an extension since I have to compare dates in repositories filled with EF data and so .Date was not an option since it is not implemented in LinqToEntities translation.

Here is the code:

        /// <summary>
    /// Check if two dates are same
    /// </summary>
    /// <typeparam name="TElement">Type</typeparam>
    /// <param name="valueSelector">date field</param>
    /// <param name="value">date compared</param>
    /// <returns>bool</returns>
    public Expression<Func<TElement, bool>> IsSameDate<TElement>(Expression<Func<TElement, DateTime>> valueSelector, DateTime value)
    {
        ParameterExpression p = valueSelector.Parameters.Single();

        var antes = Expression.GreaterThanOrEqual(valueSelector.Body, Expression.Constant(value.Date, typeof(DateTime)));

        var despues = Expression.LessThan(valueSelector.Body, Expression.Constant(value.AddDays(1).Date, typeof(DateTime)));

        Expression body = Expression.And(antes, despues);

        return Expression.Lambda<Func<TElement, bool>>(body, p);
    }

then you can use it in this way.

 var today = DateTime.Now;
 var todayPosts = from t in turnos.Where(IsSameDate<Turno>(t => t.MyDate, today))
                                      select t);

Claytor

Answer 4

If you use the Date property for DB Entities you will get exception:

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

You can use something like this:

  DateTime date = DateTime.Now.Date;

  var result = from client in context.clients
               where client.BirthDate >= date
                     && client.BirthDate < date.AddDays(1)
               select client;

Shutz

Answer 5

To do it in LINQ to Entities, you have to use supported methods:

var year = someDate.Year;
var month = ...
var q = from r in Context.Records
        where Microsoft.VisualBasic.DateAndTime.Year(r.SomeDate) == year 
              && // month and day

Ugly, but it works, and it's done on the DB server.

Soubrette

Answer 6

Here's a different way to do it, but it's only useful if SecondDate is a variable you're passing in:

DateTime startDate = SecondDate.Date;
DateTime endDate = startDate.AddDays(1).AddTicks(-1);
...
e => e.FirstDate.Value >= startDate && e.FirstDate.Value <= endDate

I think that should work

Voletta

Excellent. Worked for me. It was the explicit DateTime = x.Date; I was missing. If I used var, or had the value inline in the comparison it failed with the exception reported. Thanks. – Aversion

Glad it worked, Tim. Sorry for the delay in responding - I haven't actually logged in to SO in a while. – Voletta

If you change e.FirstDate.Value <= endDate to e.FirstDate.Value < endDate you can remove the .AddTicks(-1). – Supertanker

@MarcodeZeeuw you're right, that would definitely work as well. The conditional expression shown is intended for inclusive date comparisons of exact start and end datetimes (assuming the date range values would be passed in to the condition rather than set up in a code fragment.) IOW, the conditional is considered separate from the datetime values. – Voletta

Answer 7

You can also use this:

DbFunctions.DiffDays(date1, date2) == 0

Scolopendrid

Answer 8

you can use DbFunctions.TruncateTime() method for this.

e => DbFunctions.TruncateTime(e.FirstDate.Value) == DbFunctions.TruncateTime(SecondDate);

Lichter

Answer 9

Just always compare the Date property of DateTime, instead of the full date time.

When you make your LINQ query, use date.Date in the query, ie:

var results = from c in collection
              where c.Date == myDateTime.Date
              select c;

Amelita

I am getting the error "The specified type member 'Date' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported.". Any thoughts? – Newfashioned

Yeah - your provider doesn't handle the .Date property directly. You'll have to pull it out, and compare the dates later. – Amelita

.Date can't be used in Linq To Entities, unfortunately. Hopefully MS will add that overload support soon – Voletta

Always compare the Date property? I've googled into this comment because I have wondered if that is the best practice, ie. to always use the Date property, even when it's something like candidate.Date >= base.Date. Theoritically, the candidate.Date time must be >= 12:00:00, so using the Date property is redundant, but I'll stick with Reed's advice. – Aciniform

Answer 10

This is how I do this.

DateTime date_time_to_compare = DateTime.Now;
//Compare only date parts
context.YourObject.FirstOrDefault(r =>
                EntityFunctions.TruncateTime(r.date) == EntityFunctions.TruncateTime(date_to_compare));

Backbreaker

Answer 11

//Note for Linq Users/Coders

This should give you the exact comparison for checking if a date falls within range when working with input from a user - date picker for example:

((DateTime)ri.RequestX.DateSatisfied).Date >= startdate.Date &&
        ((DateTime)ri.RequestX.DateSatisfied).Date <= enddate.Date

where startdate and enddate are values from a date picker.

Cris

Answer 12

Without time than try like this:

TimeSpan ts = new TimeSpan(23, 59, 59);
toDate = toDate.Add(ts);
List<AuditLog> resultLogs = 
    _dbContext.AuditLogs
    .Where(al => al.Log_Date >= fromDate && al.Log_Date <= toDate)
    .ToList();
return resultLogs;

Tristis

Answer 13

You can user below link to compare 2 dates without time :

private bool DateGreaterOrEqual(DateTime dt1, DateTime dt2)
        {
            return DateTime.Compare(dt1.Date, dt2.Date) >= 0;
        }

private bool DateLessOrEqual(DateTime dt1, DateTime dt2)
        {
            return DateTime.Compare(dt1.Date, dt2.Date) <= 0;
        }

the Compare function return 3 different values: -1 0 1 which means dt1>dt2, dt1=dt2, dt1

Ikeda

Why don't you just return DateTime.Compare(dt1.Date, dt2.Date)? This makes all you need. – Betteann

Answer 14

Try this... It works fine to compare Date properties between two DateTimes type:

PS. It is a stopgap solution and a really bad practice, should never be used when you know that the database can bring thousands of records...

query = query.ToList()
             .Where(x => x.FirstDate.Date == SecondDate.Date)
             .AsQueryable();

Welldressed

P.S.: I usually use this way when the DateTimes have Time value and I want to compare only the Date. – Welldressed

this is a very bad solution, the query will get all the records, and only then filter out the dates. if the database has millions of records, this will grab all of them and only then will filter the dates. VERY BAD PRACTICE. – Phenix

It is a stopgap solution and a really bad practice, should never be used when you know that the database can bring thousands of records. – Welldressed

if you'll add your comment into your answer, i will remove my down-vote. it should be clear to anyone visiting this page that the solution you proposed is bad without having to read the comments. – Phenix

While a bad idea in general, this approach results in hugely improved performance for small record sets (<1000 records or so), because of the daft way EF translates date comparisons to SQL. I've seen queries go from over a minute to under a second just by doing the date comparison in memory instead of in whatever SQL EF generates. – Sandberg

Answer 15

I have resolved error using EfCore FromSqlRaw method.

 var sql =
            $"select * from \"ProgressBooks\" where date(\"Date\") = date('{today.Date.ToString("yyyy-MM-dd")}') and \"GroupId\" = {groupId}";
        var todayProgressBook = _context.ProgressBooks.FromSqlRaw(sql).FirstOrDefault();

Chrono

The question is not about EfCore, its more than 13 years old and is about EntityFramework! – Astto