SOQL aggregate functions do not work with Task.ActivityDate

Question

When I try the following in the Query Editor of the Developer Console:

SELECT WhoID, COUNT(Id), MIN(ActivityDate), MAX(ActivityDate)
FROM Task
GROUP BY WhoID
LIMIT 1000

I get the following error:

SELECT WhoID, COUNT(Id), MIN(ActivityDate), MAX(ActivityDate)
                         ^
ERROR at Row:1:Column:30
field ActivityDate does not support aggregate operator MIN

One would think that, given that Task.ActivityDate is a field of type Date, it should be possible to calculate the minimum/maximum value.

Actually, according to Salesforce's documentation on Support for Field Types in Aggregate Functions, the use of MIN() and MAX() with DateTime is allowed.

At least I know I am not alone, there is a post in Ideas about it.

Any idea on how I can make this work?

Answer 1

I have found a workaround by creating a custom field of type date under the Activity object called ActivityDate__c. Thus we can reformulate the initial query as

SELECT WhoID, COUNT(Id), MIN(ActivityDate__c), MAX(ActivityDate__c)
FROM Task
GROUP BY WhoID
LIMIT 1000

It is not ideal, but it's not the first time that I have had to hack around the Task/Event/Activity object to make things work as they should.

I am going to consider turning it into a "trigger formula" that gets updated before insert/update, and make the field visible only when the code is executing in G-d mode.