+ Start a Discussion
ErrorProneErrorProne 

SOQL Event 30 days

Hi,

stumped on this one. Trying to query activity data for this year. and its only delivering past 30 days worth.

 

here is my Query:

 

SELECT ActivityDate FROM event WHERE Result__c = 'Met' AND (Type= 'First Meeting - Sales' OR Type='First Meeting - Sales - REBOOK') AND ActivityDate= THIS_YEAR

 

Tried THIS_YEAR, LAST_MONTH and it still gives me the same number of records. is there a restirciton i'm unaware of? 

Best Answer chosen by Admin (Salesforce Developers) 
ErrorProneErrorProne

Got it. 

 

Its inexplicable, but the query will work if I add a CreatedDate = THIS_YEAR Param along side my ActivityDate = LAST_MONTH.

 

If anyone unerstands why this is I would love to know the reasoning

All Answers

Andrew WilkinsonAndrew Wilkinson

Try this.

 

SELECT ActivityDate FROM event WHERE Result__c = 'Met' AND (Type= 'First Meeting - Sales' OR Type='First Meeting - Sales - REBOOK') AND CALENDAR_YEAR(ActivityDate) = THIS_YEAR

 

ErrorProneErrorProne

This errors:

 

"ActivityDate does not support date function CALENDAR_YEAR"

 

 

Andrew WilkinsonAndrew Wilkinson

So is there actual data that goes back that far?

 

Also, keep in mind that there is a difference between ActivityDate and ActivityDateTime. ActivityDate is for all day events. If the isAllDayEvent flag is true then this field is used. If it is not an all day event then ActivityDateTime is used.

ErrorProneErrorProne

Yes there is definetly data, It exists in reports. and I can physically go into the events through salesforce. 

 

I was unaware about the ActivityDateTime field however this gives me the same results. as ActivityDate. 

 

 

Andrew WilkinsonAndrew Wilkinson

The only other thing I can thnk of it being is one of your other where clauses is making it restrictive.

ErrorProneErrorProne

Got it. 

 

Its inexplicable, but the query will work if I add a CreatedDate = THIS_YEAR Param along side my ActivityDate = LAST_MONTH.

 

If anyone unerstands why this is I would love to know the reasoning

This was selected as the best answer
ErrorProneErrorProne

Still can't get a grip on this. Perhaps this is an internal SFDC error. SOQL queries to EVENT that use the ActivityDate Field do not return full results:

 

here is an example:

 

SELECT ActivityDate FROM Event WHERE  ActivityDate = Last_Year

This should return all events from last year. 

instead it gives me all events dated 10/16 through the end to the year.

total results= 369

 

I know that events exist before this date because if i run this query:

SELECT ActivityDate FROM Event WHERE  ActivityDate = 2012-10-01

I get 10-15 results.

 

Adding a CreatedDate=This_Year or Last_Year, no longer works as a solution. 

 

If someone could try it in their consol and see if they have similar issue. Otherwise I'll call and open a ticket. 

 

Thanks!