using today date in Database.getQueryLocator query String throwing errors

Question

I am running a batch class. I am providing query in string format. I am facing issue with using a filter criteria StartDate__c>=:System.today()

In a direct query, i could write something like

List<Contact> allContacts = [Select Id from Contact where StartDate__c>=:System.Today()];

But in start method of batch job, i am writing this

global Database.QueryLocator start(Database.BatchableContext BC){
      String todayDate = System.today().format();
      String queryString = 'Select Id from Contact where StartDate__c>='+todayDate;
      return Database.getQueryLocator(queryString);
}

unfortunately, i am getting error - unexpected token: /

I believe the start date in string format is coming out to be 5/8/2021 and when it is concatenated to querystring, that / is causing issues. Anyway i can escape it?

Or for that matter, is there a better way to put that where clause for a date field while using String query?

Answer 1

Unless you're actually using dynamic queries (variable fields or filters), you should use inline queries.

return Database.getQueryLocator([
    Select Id from Contact where StartDate__c>=today
]);

As Reuben said, you can use date literals like "today".

I've left a doc bug to have them fix the documentation to include examples of inline queries; they are recommended and preferred when you do not need dynamic field lists or a variable number of conditions.

Answer 2

You could use date literal TODAY:
SELECT Id FROM Contact WHERE StartDate__c >= TODAY.

You could also store System.today() in a Date variable, then bind it:

Date today = System.today();
String queryString = 'SELECT Id FROM Contact WHERE StartDate__c >= :today';
return Database.getQueryLocator(queryString);

Variables binding should be the preferred way to add parameters to a query string.

Answer 3

The format you want is YYYY-MM-DD, so

global Database.QueryLocator start(Database.BatchableContext BC){
      String todayDate = System.today().year() + '-' + System.today().month() 
                         + '-' + system.today().day();
      String queryString = 'Select Id from Contact where StartDate__c>='+todayDate;
      return Database.getQueryLocator(queryString);
}

Answer 4

I'll change my own code to use an inline query with bind variable as recommended by @sfdcfox above, but if you need to use Database method, Database.getQueryLocatorWithBinds was introduced in Spring '23 (along with other variations).

Docs are here.

Here's an example:

    Map<String, Object> queryBinds = new Map<String, Object>{'subIdToAccMapKeySet' => subIdToAccMap.keySet()};
    String query = 'SELECT Id, Account__c FROM Product_Info__c WHERE Id IN :subIdToAccMapKeySet';

    return Database.getQueryLocatorWithBinds(query, queryBinds, AccessLevel.SYSTEM_MODE);