Calculate amount from datetime as date

Question

In a table i have stored alot of datetimes for when a certain action was taken. The data looks like this:

2012-05-01 14:30:29.8666925
2012-05-01 14:31:58.5422081
2012-05-01 14:35:38.3120864
2012-05-02 06:08:34.6322227
2012-05-02 06:08:54.0864203
2012-05-02 07:31:24.7269620
etc..

What i want to do is count how many times an action was taken per day. So the result i want is:

2012-05-01 = 3
2012-05-02 = 3

I tried to group the dates together, but it also takes the 'time' into account when grouping. I do need this data in my database, but not when grouping because i only want to look at it per day. Not per second.

This is what i have so far, but i'm not sure how to get the results i actually want.

var actions = (from a in entities.Actions
group a by a.Date
into g
select new
{
    Actions = g.Count() // Not sure if this line is right...
});

Anyone any idea how to do this?

The `Date` property in this example is a `string`, not a `DateTime`. You should probably include that information in your question. I updated my answer below to reflect that. — Jon Senchyna, Jun 07 '12 at 15:13
It's not a string type it is really a datetime field: datetime2(7) — w00, Jun 07 '12 at 15:22
Looking back on your question, I have no clue where I got that from. I've updated my answer to reflect a.Date being a DateTime object, not a string. — Jon Senchyna, Jun 11 '12 at 12:30
Nevermind, I see in one of your comments that you got an exception calling a.Date.ToShortDateString(). The exception stated that it was looking for System.String.ToShortDateString(), which would imply that a.Date is actually treated as a string in your C# code. Even though it may be a datetime2(7) in your DB, you could be reading it in as a strign into your C# code. — Jon Senchyna, Jun 11 '12 at 12:37
After googling your error message, I don't see anything for System.String.ToShortDateString(), but I see a different error message: "System.String ToShortDateString()", which is actually due to "ToShortDateString() being unsupported by EF. — Jon Senchyna, Jun 11 '12 at 12:47

score 2 · Accepted Answer · edited May 23 '17 at 12:21

2

Use the EntityFunctions.TruncateTime function, as detailed in this SO post: https://stackoverflow.com/a/9642321/1342632

edited May 23 '17 at 12:21

Community

1
1

answered Jun 07 '12 at 14:57

ssis_ssiSucks

1,476
1
12
11

Jon Senchyna · Answer 2 · 2012-06-11T12:43:54.357

You can simply use DateTime.Date to trim off the timestamp. After grouping, you'll want to make sure that you select the key, as well as count(), or you'll just have a bunch of counts, but you won't nkow what dates they map to. Below is an example using Date.

var actions = (from a in entities.Actions 
                // Note: if a.Date was a DateTime object, you could simply
                // group by 'a.Date.Date'
                group a by DateTime.Parse(a.Date).Date
                into g 
                select new 
                {
                    // Since count is useless on its own, store the date as well.
                    Date = g.Key,
                    Actions = g.Count()
                });

Update: I am no longer assuming that a.Date is a string. It was based on an error message received by w00 stating that "Linq to Entities doesn't recognize System.String.ToShortDateString()" when attempting to call a.Date.ToShortDateString().

I believe the actual error message is probably "LINQ to Entities does not recognize the method 'System.String ToShortDateString()'" (note how ToShortDateString() is not being call on System.String here).

This unfortunately gives me "Linq to Entities doesn't recognize System.String.ToShortDateString()" — w00, Jun 07 '12 at 14:44
Ahh, so your .Date field is a string type, I will update the above for you then. — Jon Senchyna, Jun 07 '12 at 15:01
It's not a string type it is really a datetime field: *datetime2(7)* — w00, Jun 07 '12 at 15:11

score 0 · Answer 3 · answered Jun 07 '12 at 14:38

0

Try group a by a.Date.Date

DateTime.Date gives the same DateTime but with the time component set to midnight, so it would be the same for all your dates.

answered Jun 07 '12 at 14:38

Eren Ersönmez

38,383
7
71
92

Unfortunately it says that the type "Date" is not supported by LINQ to Entities. – w00 Jun 07 '12 at 14:42
something must be wrong. There is no type "Date". DateTime.Date returns a DateTime type. – Eren Ersönmez Jun 07 '12 at 14:44

score 0 · Answer 4 · answered Jun 07 '12 at 14:39

0

in EF, I would try this:

var actions = (from a in entities.Actions
group a by a.Date.Date
into g
select new
{
    Actions = g.Count() // Not sure if this line is right...
});

...although I don't know how performant this would be.

answered Jun 07 '12 at 14:39

Jeremy Holovacs

22,480
33
117
254

Unfortunately it says that the type "Date" is not supported by LINQ to Entities. – w00 Jun 07 '12 at 14:42
1

I should have mentioned I hate Entity Framework, and this is probably not supported because it would not be very performant. You could make a calculated column on your entity to store the date without time data, and group by that, or (my preferred solution) ditch EF and go with NHibernate or ADO.NET. :) – Jeremy Holovacs Jun 07 '12 at 14:46

Calculate amount from datetime as date

4 Answers4