1

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?

2

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.

  • I found out the issue to be Date format mismatch. My debug log is showing StartDateQuarter/EndDateQuarter as YYYY-MM-DD hh:mm:ss and the created date format has YYYY-MM-DD hh:mm:ssZ . S o the Z at the end seems to be the cause . Any idea how I can correct that quickly? – SFDCRookie May 13 '16 at 13:29
  • @SFDCRookie try using convertTimezone(CreatedDate) in place of createdDate. Hope it may help you. – Saumya Ranjan Satapathy May 13 '16 at 13:36
  • One is a Date and the other is a Date-Time value. You need the Date(CreatedDate) equivalent in SOQL which I think is FORMAT (). – crmprogdev May 13 '16 at 13:42
  • @SaumyaRanjanSatapathy Unless the date is used in his groupby, that wouldn't be of any use to him, so I don't think that's what he's looking for. See developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/…. – crmprogdev May 13 '16 at 13:47
  • @SaumyaRanjanSatapathy - I am getting a "Invalid aggregate function: convertTimezone" in Dev console- Not sure why – SFDCRookie May 13 '16 at 13:50
0

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
];

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.