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

Question

_{Thursday, December 18, 2008 12:54 AM | 1 vote}

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).

All replies (35)

_{Thursday, December 18, 2008 1:40 AM}

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

For instance:

_{Thursday, December 18, 2008 1:06 PM}

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 

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:36 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 9:34 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.

_{Friday, December 19, 2008 1:46 AM}

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:

_{Friday, December 19, 2008 12:01 PM | 1 vote}

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.

_{Thursday, December 25, 2008 10:01 AM}

Hi,

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

Thanks,
Harry

_{Monday, January 5, 2009 11:57 AM}

My question is about LINQ to Entity framework, not  LINQ to SQl.

_{Tuesday, March 24, 2009 3:23 PM | 4 votes}

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)))

_{Sunday, July 5, 2009 4:49 AM | 1 vote}

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.

_{Monday, July 6, 2009 10:37 AM | 1 vote}

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 6, 2009 12:02 PM}

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 20, 2009 12:03 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, January 4, 2010 4:14 PM | 3 votes}

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

_{Tuesday, January 5, 2010 3:01 PM | 1 vote}

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.

_{Sunday, January 2, 2011 3:38 AM}

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 2, 2011 3:39 AM}

Doing so much work arounds just for date comparision is so frustrating..

_{Monday, January 3, 2011 1:09 PM}

Which workaround?

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

_{Monday, January 3, 2011 10:07 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'

*
*

_{Tuesday, January 4, 2011 10:12 AM | 1 vote}

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;

_{Friday, January 7, 2011 2:41 PM | 1 vote}

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)

_{Monday, January 10, 2011 8:29 AM}

Sorry, bit quick to post.

:)

_{Thursday, February 10, 2011 4:51 PM | 1 vote}

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

_{Wednesday, April 20, 2011 4:03 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

_{Sunday, June 5, 2011 12:11 AM | 30 votes}

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 5, 2011 5:28 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

_{Thursday, June 16, 2011 5:26 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

_{Tuesday, July 12, 2011 6:10 PM}

Thanks for the link Saadi03, but this discussion is about Linq2Entities, not Linq2Sql.

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

_{Wednesday, July 13, 2011 11:20 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).

_{Monday, September 12, 2011 10:08 AM}

Thanks Jumes you really saved my day.

_{Wednesday, April 18, 2012 8:25 AM}

Thank you for the information!

_{Friday, February 22, 2013 1: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.

_{Thursday, March 14, 2013 3:20 PM}

Thanks this works!!!

Anton Swanevelder eSite

_{Thursday, March 21, 2013 7:23 AM}

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

_{Tuesday, November 12, 2013 5:11 PM | 1 vote}

For the sake of completeness, in EF 6.0 this function is located in the following class:

System.Data.Entity.DbFunctions