Adding Date criterion in WHERE Clause

Question

Here I am doing an aggregate SOQL

List<AggregateResult> ListName = [select SUM(Field1__c), SUM(Field2__c), 
                      from CustomObject__c  
                      where Object2__r.Id In :List2 
                      AND ( createdDate>=: StartDateQuarter 
                      AND createdDate<=: EndDateQuarter ) 
                      GROUP BY Object2__r.Name, Object2__r.Id ];

This is where I am determining the dates.

List<Period> LastDates = [Select type, StartDate, EndDate From Period WHERE type = 'Quarter' AND StartDate = LAST_N_QUARTERS:4 LIMIT 1];
Datetime StartDateQuarterLast = LastDates[0].StartDate;
Datetime EndDateQuarterLast = LastDates[0].EndDate;       

This query is working when I am removing the DATE part. NOT working with the DATE filter.

StartDateQuarter and EndDateQuarter are getting values. I have checked that.

Is there any syntactical error?

Answer 1

I can find no reference in the SOQL documentation to StartDateQuarter or EndDateQuarter. If those are custom fields, the may need to be appended with __c and you'd need to remove the colons that precede them.

Otherwise, based on the SOQL Documentation for DateFormats, I believe the date part of your query needs to be revised to use one or more of the following to specify the quarter:

THIS_QUARTER, LAST_QUARTER, NEXT_QUARTER, NEXT_N_QUARTERS:n, LAST_N_QUARTERS:n, THIS_FISCAL_QUARTER, LAST_FISCAL_QUARTER, NEXT_FISCAL_QUARTER, NEXT_N_FISCAL_​QUARTERS:n, or LAST_N_FISCAL_​QUARTERS:n.

To do this, you may need to run 2 queries, one to determine the "relative" quarter in which the created date falls in and then your final query.

Answer 2

I think you just need to make sure you get the correct Period record by sorting them newest first.

List<Period> recentQuarters = [
    SELECT StartDate, EndDate
    FROM Period
    WHERE Type = 'Quarter'
    ORDER BY StartDate DESC
    LIMIT 1
];