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?

score 2 · Answer 1 · answered May 13 '16 at 13:18

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.

answered May 13 '16 at 13:18

crmprogdev

40,955
9
58
115

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 https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_convert_time_zone.htm?search_text=converttimezone. – 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
@SFDCRookie I belive your query is not working because you are comparing date in two different time zone. Try to convert both to a single time zone, may be by using Datetime.valueOf() or Datetime.valueOfGmt(). – Saumya Ranjan Satapathy May 13 '16 at 13:56
@SFDCRookie Are you getting query syntax error or just the result of the query is blan? I hope you are getting the second one. – Saumya Ranjan Satapathy May 13 '16 at 13:57
@SFDCRookie I don't think selecting a TimeZone is the solution to your problem. Try using FORMAT(). If you do try a TimeZone, simply use GMT as the TZ is irrelevant, thus the reason you want to use Format to convert to Date. – crmprogdev May 13 '16 at 13:59
@crmprogdev -I assumed the Z criterion because it worked with .CreatedDate > 2011-01-01T00:00:00Z and o.CreatedDate < 2011-12-31T00:00:00Z - So you mean I should use GMT for both ?Where should I use the FORMAT ? It will not allow me to use in the WHERE clause – SFDCRookie May 13 '16 at 14:09
Try using DATE(CreatedDate). FORMAT() in apex would convert that to a string. In apex, DATE() would convert a date-time to a date. SOQL should see the same behavior. – crmprogdev May 13 '16 at 14:30
But Format can only be used in Select query . It is not allowed in the WHERE clause or any other place. – SFDCRookie May 13 '16 at 14:37
If DATE() doesn't work for you (FORMAT is not what you need), you may have hit a wall if you want to see results by quarter unless you run this as two queries to first get a map of Quarter to CreatedDate. – crmprogdev May 13 '16 at 14:52
this is what I found: String formattedDt = dt.format('yyyy-MM-dd\'T\'hh:mm:ss\'Z\''); should dod the trick, or you can even try formatGmt(dateFormatString) from here – o-lexi May 13 '16 at 16:09
@Oleksiy The issue is that he needs to use the CreatedDate QUERY FIELD in his WHERE clause as a DATE FIELD (not a value), so he doesn't need to format an actual string. Instead, he needs the SOQL engine to convert the Date-time field to read it as a DATE field when constructing & running the query. He's not passing the data into the query. It's collecting it as part of the query. – crmprogdev May 13 '16 at 16:17
@SFDCRookie The only error I get when try to format CreatedDate criteria is value of filter criterion for field 'CreatedDate' must be of type dateTime and should not be enclosed in quotes, so DateTime.newInstanceGMT(LastDates[0].StartDate, Time.newInstance(0, 0, 0, 0)); should work – o-lexi May 13 '16 at 17:02

score 0 · Answer 2 · answered May 13 '16 at 17:12

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

answered May 13 '16 at 17:12

Adrian Larson

149,971
38
239
420

Adding Date criterion in WHERE Clause

2 Answers2