1

First off, I am not sure how to properly refer to this type of SOQL query. I am working on a batch process and this is the step where you build the query.

What I am having trouble with is putting in a variable that represents a date that is 60 days ago. Can someone tell me the proper way of doing this? Every time I try and execute this code I get an error related to the date variable.

global Database.QueryLocator start(Database.BatchableContext bc) {
        date d1 = system.today()-60;
        string ds1 = d1.format();

        return Database.getQueryLocator(
            'SELECT Account_Benefit__c, Account_ID__c, Employee__c, Employee__r.Latest_Term_or_Audit_Date__c, ' +
            'Grand_Total_Premium__c, Name ' +
            'FROM Employee_Benefit_Summary__c ' +
            'WHERE Employee__r.Account__r.Separated_EE_Auto_Zero_Benefits__c = TRUE ' +
            'AND Employee__r.Latest_Term_or_Audit_Date__c < ' + ds1 +
            'AND Grand_Total_Premium__c != 0'
        );
    }
1
  • 2
    side note: use inline soql when possiblre. Using a string is inherently risky.
    – sfdcfox
    Commented Jul 17, 2018 at 20:23

1 Answer 1

3

Two options:

  1. Create an instance of Date, based on today, then subtract 60 days from that date. AddDays is a bit misleading, since you can add a negative number of days to the date.

    Date.today().addDays(-60); 
    
  2. Use the LAST_N_DAYS date literal. This can be used to select a period of time (via =), or a period before or after the given period of days (via < or >).

    Latest_Term_or_Audit_Date__c < LAST_N_DAYS:60
    

Personally, I prefer LAST_N_DAYS, since you don't have to do any formatting, but you might need that date for something else.

4
  • 2
    LAST_N_DAYS is definitely preferred with Dynamic SOQL wherever possible. Otherwise, you must use String.valueOf(someDate) - string concatenation results in the wrong Date format for SOQL.
    – David Reed
    Commented Jul 17, 2018 at 19:07
  • @DavidReed rather than using String.valueOf(someDate), why not just use a bind variable? They work in dynamic SOQL too, and would allow you to add some more interesting logic (if necessary) to arrive at the date value before your query. Date someDate = Utils.addBusinessDays(System.today(), -17); return Database.getQueryLocator('select Id from SomeObj__c where Date__c < :someDate');
    – JCD
    Commented Jul 17, 2018 at 22:08
  • @JCD Binds are a better solution possible, you're right. It just adds a dependency on the local scope that occasionally isn't workable depending on how you're building/storing the query.
    – David Reed
    Commented Jul 17, 2018 at 22:23
  • I believe the notation should be: Latest_Term_or_Audit_Date__c = LAST_N_DAYS:60 (with an equals) because LAST_N_DAYS signifies a range of values. See the linked documentation on the answer. Commented Sep 18, 2018 at 14:12

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .