Is there a way to group by the date portion of a datetime field in SOQL?

Question

Is there a way (without creating a formula datevalue(datetime) field) to aggregate a SOQL query on the date portion of a datetime field? For example, I'd like to do something like:

select datevalue(datetimeField), count(Id) from object__c group by datevalue(datetimeField)

score 12 · Accepted Answer · answered Feb 08 '12 at 04:45

12

While you can't group on a datetime field directly, there are a number of date/time functions for aggregates, so that you can group by part of the dateTime value. For example here's a query that'll show the number of account records created on each date.

select day_only(createdDate) createdonDate, 
    count(createdDate) numCreated 
    from account 
    group by day_only(createdDate) 
    order by day_only(createdDate) desc

The docs have details of all the date functions for aggregates.

day_only() returns the date part of a dateTime field.

answered Feb 08 '12 at 04:45

superfell

18,780
4
59
81

That's good to know! Was also wondering if maybe using a formula field would work, though I've not tried it. – Matt Lacey Feb 08 '12 at 07:02
I completely missed day_only() when I read the docs. Thanks for pointing that out - it's exactly what I was looking for. – barelyknown Feb 08 '12 at 11:23

score 1 · Answer 2 · answered Feb 08 '12 at 04:32

1

According to the DescribeSObjectResult, datetime fields are not "groupable".

answered Feb 08 '12 at 04:32

Jeremy Ross

11,544
3
36
36

Is there a way to group by the date portion of a datetime field in SOQL?

2 Answers2