[SalesForce] Using a specific dynamic date in a SOQL query where clause

I am trying to select a set of records in a batchable class that have expiration dates that are 7 or 30 days out. For the rest of the post I will only show 30 for brevity.

I tried setting my query string like:

String query = 'Select Id From Account Where Membership_Expiration_Date__c = ' + System.today().addDays(30);

Which doesn't work because it formats it this way in the query string:

2016-09-23 00:00:00

I looked at the date literals and I didn't see anything to give me a certain day, other than today/yesterday/tomorrow, the rest all give ranges.

I also tried adding DATE_ONLY(System.today().addDays(30)) and that errored out on me. Expecting a colon before DATE_ONLY

I finally ended up adding the following to my where clause:

...String.valueOf(System.today().addDays(30)).substringBefore(' ');

Which works, but I feel like I am missing something obvious.

What is the best/easiest way to query records that are equal to the date n days from now?

Best Answer

For a formatting solution, try something like:

public static String getSoqlDate(Integer daysFromNow)
{
    return Datetime.now().addDays(daysFromNow).formatGmt('yyyy-MM-dd');
}
public static String getSoqlDatetime(Integer daysFromNow)
{
    return Datetime.now().addDays(daysFromNow).formatGmt('yyyy-MM-dd\'T\'HH:mm:ss.SSS\'Z\'');
}

Also consider using bind expressions. They are context dependent, but they should work in a batch. Play around with the scope, but it should work with:

Date days_out_7 = Date.today().addDays(7);
Date days_out_30 = Date.today().addDays(30);
String query = '...' + 'WHERE Date__c IN (:days_out_7, :days_out_30)';
Related Topic