none
Ignore time portion of datetime in a LINQ to Entity Framework query

    Question

  • How can I do a LINQ to Entity Framework query where you ignore the time portion of a datetime column? Using something like MyTable.MyColumn.Date is not supported when using LINQ TO EF (think it works with LINQ to SQL) and as far as I know you can't use a Convert/cast like you can in normal SQL (or can you?) 

    I need this in the where statement trying to do something like "... where mytable.datecolumn = (select min(datecolumn) ...) " but ignoring timepart (want to select all rows where date is same as the date returned from the subquery).
    • Changed type Harry Zhu Thursday, December 25, 2008 10:01 AM
    • Changed type Harry Zhu Tuesday, January 13, 2009 4:54 AM
    Thursday, December 18, 2008 12:54 AM

All replies

  • You can treat it like a regular DateTime except that it is nullable (i.e. DateTime?).

    For instance:

    var x = myContext.MyTable.Where(i => i.MyDateColumn.GetValueOrDefault().Date   
    ... ); 
    • Proposed as answer by Dhaval Jaiswal Friday, November 22, 2013 5:58 PM
    Thursday, December 18, 2008 1:40 AM
  • Not sure I understand you. What has nullable to do with it?

    Anyway, there is no GetValueOrDefault but hat might be because my column is not nullable.

    If I do something like 
    var x = myContext.MyTable.Where(i => i.MyDateColumn.Date == DateTime.ToDay)

    Then I will get an error like:

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

    LINQ to Entities does not accept using the Date member. I think it do work when using LINQ to SQL though. So the question is how to do it?
    Thursday, December 18, 2008 1:06 PM
  • What is your column type?  If it is a datetime, then there's nothing special you have to do.  i.MyDateColumn should give you a DateTime.
    Thursday, December 18, 2008 1:36 PM
  • It is a datetime but I want to ignore the time portion of the datetime value. I am selecting on a datetime column which have time portion of date time (ie not 00:00 time) comparing it to a datetime which does not have a time. I want to select those where date is equal to the other date ignoring the time.

    For example like in T-SQL ... where Convert(varchar(8),tableA.datecol,112) = Convert(varchar(8),TableB.dateCol,112) that convert would ignore time and instead use a string in yyyymmdd format for comparing. Want to do something similar with Linq to Entities. With Linq to SQL I think I could use tableA.datecol.Date = TableB.dateCol.Date and the generated SQL statement would use a convert/cast.
    Thursday, December 18, 2008 9:34 PM
  • Magnus Bergh said:

    It is a datetime but I want to ignore the time portion of the datetime value. I am selecting on a datetime column which have time portion of date time (ie not 00:00 time) comparing it to a datetime which does not have a time. I want to select those where date is equal to the other date ignoring the time.

    For example like  ... where Convert(varchar(8),tableA.datecol,112) = Convert(varchar(8),TableB.dateCol,112) that convert would ignore time and instead use a string in yyyymmdd format for comparing. Want to do something similar with Linq to Entities. With Linq to SQL I think I could use tableA.datecol.Date = TableB.dateCol.Date and the generated SQL statement would use a convert/cast.



    Oh, okay, gotcha.  Try this:

    var x = myContext.MyTable.ToList().Where(i => i.MyDateColumn.Date == DateTime.ToDay)
    Friday, December 19, 2008 1:46 AM
  • Thanks.

    However doesn't having the ToList() mean that this actually will execute the query (on server) and that the where expression will be executed locally. Meaning it will get all rows from the table from server and then filtering it locally.

    Yeah, I run a test and no where statement on the generated query getting all rows.

    Would like to evaluate this on the server since the date selection is one part of the selection where I compared a date column with a min date value from a subquery. for example  ... mytable. datecol = (from mytable 2 ... select mytable2.datecol).Min()

    ie "retrun all rows where date is equal to the first date (same day not checking time) in subquery.
    Friday, December 19, 2008 12:01 PM
  • Hi,

    For questions and discussion about LINQ to SQL,Please see:http://social.msdn.microsoft.com/Forums/en-US/linqtosql/threads/

    Thanks,
    Harry
    Thursday, December 25, 2008 10:01 AM
  • My question is about LINQ to Entity framework, not  LINQ to SQl.
    Monday, January 05, 2009 11:57 AM
  • Hi magnus999

    Not sure if this helps, but the following would work:

    var x = myContext.MyTable.Where(i => (i.MyDateColumn >= DateTime.ToDay) && (i.MyDateColumn < DateTime.ToDay.AddDays(1)))
    Tuesday, March 24, 2009 3:23 PM
  • Thank you LAAtkinson!

    I HATE it when Microsoft want you to use something new (Entity Framework) over something that came before (LINQ to SQL) but don't provide the means to do the same things as easily as you could before. I've had similar problems with something as simple as binding to a combo box (sorted now, but with ANOTHER ugly, manual workaround).

    Don't get me wrong, I actually love EF, but having to resort to workarounds like this, when the previous technology allowed you to use standard methods (like MyDateColumn.Date) is just plain frustrating!

    Yann

    PS - I also hate it when people don't read the question properly before supplying an answer (which doesn't answer the actual question being asked), lol.
    Sunday, July 05, 2009 4:49 AM
  • PS - I also hate it when people don't read the question properly before supplying an answer (which doesn't answer the actual question being asked), lol.

    Right.  Then I apologize for attempting to help you out.
    Monday, July 06, 2009 10:37 AM
  • Stanley,

    I'm sorry you feel targeted by my off-hand comment. You were *part* of the trigger for the thought process, but not the *only* cause. Harry did exactly the same thing & it was the fact that two people had done the same thing that made me speak up. I've seen it MANY happen times. Far too many times.

    Your intent is much appreciated, well, certainly by myself, but you'd have to agree that answers to questions that weren't asked could be very frustrating for the original poster (& those with the same problem) who needs to get his problem solved, but has to spend time explaining that the person answering didn't understand what was meant, then has to wait even longer for perhaps the right the answer to come along.

    But, don't take it personally, it wasn't meant as such. And maybe think just a little bit more carefully if you want to help out that your reply is actually answering the question that was asked, that's all.

    Yann
    Monday, July 06, 2009 12:02 PM
  • Hi Stanley,

    Lets try this, I hope it will solve your problem. I tried this and it solved my problem.

    var query= (from t in context.table.AsEnumerable()

    where t.Field == 'abc' &&

    orderby t.ExpiryDate descending

    select new ExtentionForExpiry

    {

    EntryDate = t.EntryDate.ToShortDateString(),

    ExpiryDate = t.ExpiryDate.Value.ToShortDateString(),

    DateCollected = t.DateCollected.HasValue? t.DateCollected.Value.ToShortDateString():null

    }

    ).Distinct().ToList();

     Regards

     Hina Bashir ,
    Monday, July 20, 2009 12:03 PM
  • I tried to use this:

                       where r.MyDate.Date == DateTime.Now.Date

     but I received the same error. I used this as a workaround:

                       where r.MyDate.Year == DateTime.Now.Year
                                && r.MyDate.Month == DateTime.Now.Month
                                && r.MyDate.Day == DateTime.Now.Day

    It appears the 'Date' datatype is not supported, where as Year, Month and Day are 'int' datatypes and are supported.

    If anyone can verify this, I'd appreciate it. Thanks!!

    -Vinny Davi

    • Edited by Vinny Davi Monday, January 04, 2010 4:20 PM variable change
    Monday, January 04, 2010 4:14 PM
  • It seems there is no problem using the DateTime.Date property
    I just made this query:

    var orders = from o in northwind.Orders
                 where o.OrderDate.Date == DateTime.Today
                 select o;

    and it constructed this:

    SELECT [t0].[Order ID] AS [OrderID], [t0].[Order Date] AS [OrderDate]
    FROM [Orders] AS [t0]
    WHERE DATEADD(HOUR, -DATEPART(HOUR, [t0].[Order Date]), DATEADD(MINUTE, -DATEPART(MINUTE, [t0].[Order Date]), DATEADD(SECOND, -DATEPART(SECOND, [t0].[Order Date]), DATEADD(MILLISECOND, -DATEPART(MILLISECOND, [t0].[Order Date]), [t0].[Order Date])))) = @p0

    Maybe the problem is in the db. I've had problems with dates on an AS/400 database.


    Tuesday, January 05, 2010 3:01 PM
  • Don'g get me wrong too. I love microsoft technologies, but in order to hurry releasing something, they make developers do lots of wierd workarounds. Why on the earth they promote Entity Framework if the simplemost Date comparision is not working, which is most frequently used part of business applications. It was working fine with LINQ to SQL.

    Sunday, January 02, 2011 3:38 AM
  • Doing so much work arounds just for date comparision is so frustrating..
    Sunday, January 02, 2011 3:39 AM
  • Which workaround?

    I showed a query that uses the Date property without problem.

    Monday, January 03, 2011 1:09 PM
  • I can't get your query to work with Entity Framework 4 is your query for linq to sql only? There is no .date value.

    From
     t In
     context.mytable Where
     t.myDate.GetValueOrDefault.Date = DateTime
    .Today
    

    LINQ to Entities does not recognize the method 'System.DateTime GetValueOrDefault()' method, and this method cannot be translated into a store expression.

    Tried this...
    From
     t In
     context.myTable Where
     t.DepartDate = DateTime
    .Today

    Ef4 generated sql where clause...
    WHERE [Extent1].[DepartDate] = @p__linq__0',N'@p__linq__0 datetime',@p__linq__0=''2011-01-04 00:00:00:000''

    This will only select items that have this time value ' 00:00:00:000'


     

    Monday, January 03, 2011 10:07 PM
  • I just had the same problem. I wanted to have all the newsitems where the end date of the newsitem was bigger or the same as today.

    IEnumerable<NewsItem> newsItemsDB = 
      from n in entities.NewsItems
      where n.EndDate >= DateTime.Today
      select n;
    

    Tuesday, January 04, 2011 10:12 AM
  • So now we're not only replying to a thread that started (and by the looks of it, should have died) in 2008, but we're also posting code in kiddy language on the c# forums?

    ;-)

     


    "The improbable we do, the impossible just takes a little longer." (Steven Parker)
    Friday, January 07, 2011 2:41 PM
  • Sorry, bit quick to post.

     

    :)

    Monday, January 10, 2011 8:29 AM
  • Hey Vinny,

    More than a year later, I've come across the same problem.

    My solution:

     

    where SqlFunctions.DateDiff("day", r.MyDate.Date, DateTime.Today) == 0

     

    Thursday, February 10, 2011 4:51 PM
  • Hey Vinny,

    More than a year later, I've come across the same problem.

    My solution:

     

    where SqlFunctions.DateDiff("day", r.MyDate.Date, DateTime.Today) == 0

     

    Still obtaining the annoying "The specified type member 'Date' is not supported in LINQ to Entities..." here using the DateDiff
    Wednesday, April 20, 2011 4:03 PM
  • Use the class EntityFunction for trimming the time portion.

     

    using System.Data.Objects;


         ....

     

     var bla = (from log in context.Contacts
                       where EntityFunctions.TruncateTime(log.ModifiedDate) < today.Date
                       select log).FirstOrDefault();
    Sunday, June 05, 2011 12:11 AM
  • Merci Francois!

    Excellent find. I didn't even realise that EntityFunctions existed.

    Be aware, anyone who wants to use this, that if your project doesn't already have it, that it will need a reference to System.Data.Entity

    Yann

    Sunday, June 05, 2011 5:28 AM
  • i think this blog should be visited by all the beginners of linq to sql, it contains a video tutorial plus code samples, its a great gift for any beginner, the url is

    http://sexyselect.net/blog/post/2011/06/15/Part1-Beginners-Guid-to-Linq-to-Sql.aspx

    visit this blog i am sure every one find it help full.  


    saadumar46
    Thursday, June 16, 2011 5:26 AM
  • Thanks for the link Saadi03, but this discussion is about Linq2Entities, not Linq2Sql.

    With that said, thank you François Jumes for your solution!

    Tuesday, July 12, 2011 6:10 PM
  • Thanks for the suggestion, François!

    When you have a lot of data though, and are looking for better performance (depending on how you are using indexes), you might compare this to LAAtkinson's suggestion for datetime comparison between two dates (today and today+1).


    Wednesday, July 13, 2011 11:20 PM
  • Thanks Jumes you really saved my day.
    Monday, September 12, 2011 10:08 AM
  • Thank you for the information!
    Wednesday, April 18, 2012 8:25 AM
  • To all thanks for the chat content. It was great to find out about EntityFunctions but I am also interested in the server side vs client side comments as well. I find the date selection frustrating but this solves it nicely for me.

    Friday, February 22, 2013 1:25 AM
  • Thanks this works!!!

    Anton Swanevelder eSite

    Thursday, March 14, 2013 3:20 PM
  • You can avoid error :


    where Convert.ToDateTime(r.MyDate).Year == DateTime.Now.Year

    && Convert.ToDateTime(r.MyDate).Month == DateTime.Now.Month

    && Convert.ToDateTime(r.MyDate).Day == DateTime.Now.Day


    Thursday, March 21, 2013 7:23 AM
  • For the sake of completeness, in EF 6.0 this function is located in the following class:
    System.Data.Entity.DbFunctions 
    Tuesday, November 12, 2013 5:11 PM