7

i heard people saying date time comparison do not work just due to time part because datetime has time part.

in sql i always compare datetime like this way and it works fine

select * from employee
where convert(varchar,dob,112) > '20111201' // this yyyymmdd format.

how could i simulate this in a LINQ query?

abatishchev
  • 98,240
  • 88
  • 296
  • 433
Thomas
  • 33,544
  • 126
  • 357
  • 626
  • 1
    Do you mean both Entity Framework (=LINQ to Entities) and LINQ to SQL (pre EF ORM)? – abatishchev Sep 13 '12 at 18:24
  • 1
    FWIW, if you're using SQL Server 2008 or above, you should probably be using `Date` (rather than `DateTime`) for fields whose time portion is irrelevant (it's unlikely that you would actually HAVE the time of day that someone was born, and even less likely that it would be meaningful to you). Using `DateTime` when `Date` is available and you don't care about the time would be like storing all of your numeric data in a `float` regardless of whether or not the data is inherently integral. – Adam Robinson Sep 13 '12 at 18:26

2 Answers2

10

If you're using .NET 4 or above, just use the EntityFunctions.TruncateTime helper method. This will translate this type of datetime-to-date conversion to SQL for you.

from e in EfEmployeeContext
where EntityFunctions.TruncateTime(e.DOB) > new DateTime(2011,12,01);
mclark1129
  • 7,532
  • 5
  • 48
  • 84
  • 3
    If you're using `LINQ to SQL` then I would remove the tags to `EntityFramework` and `linq-to-entities`, as well as the reference to EF in your question title. Using LINQ to SQL means that your question becomes irrelevant. – mclark1129 Sep 13 '12 at 18:25
  • date comparison can't be there when people use LINQ to SQL? – Thomas Sep 13 '12 at 18:37
  • 1
    That's not what I'm saying, I'm saying that your question is referencing EntityFramework not LINQ to SQL. It'd be the same as asking "How do I do *x* in C#" and then responding "Thanks, but how would I do this in Java?". – mclark1129 Sep 13 '12 at 18:40
  • 4
    EntityFunctions.TruncateTime(e.DOB) is obsolete, batter try this instead System.Data.Entity.DbFunctions.TruncateTime(e.DOB) – X-Coder Aug 24 '15 at 05:26
3

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);
KeithS
  • 70,210
  • 21
  • 112
  • 164
  • does this code from e in EfEmployeeContext where e.DOB > new DateTime(2011,12,01); work in all culture because USA date format is different where UK has different. – Thomas Sep 13 '12 at 18:35
  • The constructor of a DateTime always takes the year first, then month, then day, regardless of the culture that may be used to display it as a string. – KeithS Sep 13 '12 at 20:39