How to Compare Only Date Components from Datetime in Ef

How to compare only date components from DateTime in EF?

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
}

How to compare only Date without Time in DateTime types in Linq to SQL with Entity Framework?

try using the Date property on the DateTime Object...

if(dtOne.Date == dtTwo.Date)
    ....

Exception on try to compare only date from datetime with ef core 3.0

It seems DateTime.Now is the issue here.

I don't know the data, and I don't know if you store the time component, but there are two options you could try.

If you want to compare two timestamps, take the .Now out of the lamba and try the following.

var today = DateTime.Now.Date; // Or DateTime.Today
Context.Category.Where(c => c.CreateAt.Date == today ).AsNoTracking().ToListAsync();

If you want all records for today you can try the following.

var start = DateTime.Today;
var end = Date.Time.Today.AddDays(1); // the following midnight

var todaysCats = Context.Category.Where(c => c.CreateAt >= start && c.CreateAt < end ) // note '>=' and '<'

---

BTW. It' better to use Utc timestamps for CreatedAt-like fields. It pays off in the long term.

How to compare date in Entity Framework?

Why bother converting to a string again?? That most likely breaks your code, since the .ToString() isn't guaranteed to return the data representation you're expecting.

Just leave the date as a date and compare like this:

DateTime desiredDate = new DateTime(2017, 12, 15);
var list = db.tmpListAction.Where(e => e.StartTime == desiredDate).ToList();

How do I perform date-part comparison in EF

The one thing to keep in mind is that operations on DateTime structs that represent database columns don't translate to SQL. So, you cannot write a query like:

from e in EfEmployeeContext
where e.DOB.Date > new DateTime(2011,12,01);

... because e.DOB represents the DOB column in the database, and EF won't know how to translate the Date sub-property.

However, there's an easy workaround depending on what dates you want:

• If you want to include all employees that have a DOB on 12/01/2011 as well as those born after that date, then simply query:

  from e in EfEmployeeContext
  where e.DOB > new DateTime(2011,12,01);
  

• If you want to include only employees born after 12/01/2011, then query:

  from e in EfEmployeeContext
  where e.DOB >= new DateTime(2011,12,02);
  

In short, the criteria, meaning a constant or literal DateTime you're comparing against, can be set up however you want. You just can't make radical modifications to properties that represent DB columns within the where predicate. That means you can't compare one DateTime column to a projection of another DateTime column, for instance:

    //get all employees that were hired in the first six months of the year
    from e in EfEmployeeContext
    where e.HireDate < new DateTime(e.HireDate.Year, 7, 1);

Compare only time from datetime in entity framework 6 with odp.net Oracle 12c

Because of the date- and time-problems with oracle, we do it string-only:

using(MyDbContext ctx = new MyDbContext())
{
    TimeSpan myTime = new TimeSpan(12, 00, 00);
    string myTimeString = myTime.ToString("hh':'mm':'ss");
    List<ExecutionObjects> tmp = ctx.ExecutionObjects.Where(a => a.ExecutionTime.EndsWith(myTimeString)).ToList();

    // Access field in source with seperated DateTime-property.
    tmp.ForEach(e => Console.WriteLine(e.ExecutionTimeDateTime.ToShortDateString()));
}

In the source you can add an DateTime-parsing-property:

public class ExecutionObject
{
    [Column("ColExecutionTime")]
    public string ExecutionTime { get; set; }
    [NotMapped]
    public DateTime ExecutionTimeDateTime {
        get
        {
            return DateTime.ParseExact(this.ExecutionTime, "yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture);
        }
        set
        {
            this.ExecutionTime = value.ToString("yyyy-MM-dd HH:mm:ss");
        }
    }
}

Not the most beautiful version, but working.

This is an Oracle-based problem in the DbFunctions. If you activate the sql-log you'll see that a function "CREATETIME()" is used, which is unknown.

Activate sql-log:
ctx.Database.Log = Console.WriteLine;

The log will look like this:

SELECT *
  FROM "ExecutionTimes" "Extent1"
 WHERE ((((CREATETIME (EXTRACT (HOUR FROM (CAST (
[...]

---

Related Topics