# Group By Date (but ignoring the time portion)

### Question

• Something tells me this should be easier than I think.

from i in ADB.ListItem

group i by i.EffectiveDate into g

select g

but with dates like 5/10/2008 06:33:11 the grouping doenst work right.

I need it to group by the Date portion of the date & ignore the time portion, I thought maybe:

from i in ADB.ListItem

group i by i.EffectiveDate.Date into g

select g

But that errors out as Linq to Entities doesn't know about .Date.

What is the trick?

Thanks!

Tuesday, May 13, 2008 9:16 PM

### Answers

•

Hi Ryan,

Another option is to do:

Code Snippet

var q = from i in ABD.Listitem

let dt = p.EffectiveDate

group i by new { y = dt.Year, m = dt.Month, d = dt.Day}

select g;

Thanks,

Kati

Wednesday, May 14, 2008 12:08 AM

### All replies

• Hi Ryan,

Since there is no function we support to extract the Date portion of a DateTime, you can use the trick explained in this other thread:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3274604&SiteID=1

Basically, it is about creating a function from DateTime into Int that preserves the order and also give you distinct values for the cases that you care about.

Code Snippet

var q = from i in ABD.Listitem

let d = p.EffectiveDate

group i by (d.Year - 2000) * 384 + d.Month * 32 + d.Day into g

select g;

I don't have your model, and so I have just compiled this query in my head . Let me know if it works for you. Also, I know this doesn't look pretty. We plan expanding LINQ to Entities to support translating more constructs in future versions.

Hope this helps,

-Diego

Tuesday, May 13, 2008 11:20 PM
•

Hi Ryan,

Another option is to do:

Code Snippet

var q = from i in ABD.Listitem

let dt = p.EffectiveDate

group i by new { y = dt.Year, m = dt.Month, d = dt.Day}

select g;

Thanks,

Kati

Wednesday, May 14, 2008 12:08 AM
• I think what Kati suggests is actually the exact thing you should do in this case.

I often forget the fact that you can use this anonymous type syntax to define your group key in LINQ.

The solution based on the monotonic function I proposed is more useful when you actually want to do other kind of comparisons like inequalities. You don't need it to do a GROUP BY.

Other than that, I actually tried to compile the code this time and I found a few things to fix

Code Snippet

var q = from i in ABD.Listitem

let dt = i.EffectiveDate

group i by new { y = dt.Year, m = dt.Month, d = dt.Day } into g

select g;

-Diego
Wednesday, May 14, 2008 12:21 AM
•

Thank you both, both solutions worked actually.

I am using the second, but it was nice to think about how the first worked,

Ryan

Wednesday, May 14, 2008 12:23 AM
• Code Snippet

var q = from i in ABD.Listitem

let d = p.EffectiveDate

group i by (d.Year - 2000) * 384 + d.Month * 32 + d.Day into g

select g;

Why 384 * year ??
why 32 * Month ?...

Instead you should simply toString it all : ""  + d.Year + d.Month + d.Day (maybe including a separator)
The anonymous Key is the better of the two solutions, but your first example would yield a Key that couldn't be used afterwords.

Thursday, November 10, 2011 9:07 AM
• Erm ...

2011-1-23 -> 2011123

2011-12-3 -> 2011132

Plus ... does this actually work? Seems you are using VB.Net that issues a ToString() on the numbers returned by d.Year automatically, but ToString() is not supported by LINQ to Entities. I don't want to touch VB so I can't test it, but I do believe

group i by d.Year.ToString() + "-" + d.Month.ToString() + "-" + d.Day.ToString()

in C# would compile, but not run.

Jenda

----------------------------------
http://jendaperl.blogspot.com
A Perl developer in the world of C#
Thursday, November 10, 2011 1:28 PM
• Yea... hehe... I was tinking in zeropadded date and month -> 20110102

but you are maybe right, that the toString() fails in Entity Framework...

Thursday, November 10, 2011 2:06 PM
• var q = from i in ABD.Listitem

group i by i.SomeDateTime.Date into g

`public DateTime Date { get; }`

Gets the date component of this instance.

Tuesday, March 27, 2012 3:03 PM