Date comparisons in Dynamic SOQL queries

By Jeff Ballard

When you start development in Salesforce using Apex classes, you might struggle at first with building dynamic SOQL queries, specifically when it comes to comparing non-string values, such as dates.

To start, we have a statement like this, which will query active "Resources" objects:

List<Resource__c> resources = Database.query('Select Id, Name FROM Resource__c WHERE Status__c = \'Active\'');

But what if you also want to limit your resources by a date?

There is a custom Date field on the Resource object Start__c, and you want to query only Active Resources with a Start__c value on or before today. The first thing you might try is a date comparison using a string representation of the date since the query is a string:

Date today = Date.today();

List<Resource__c> resources = Database.query('Select Id, Name FROM Resource__c WHERE Status__c = \'Active\' and Start__c <= ' + today.format());

The above statement returns an error saying that date comparisons must use a Date object.

Something like the below won't work, either, because we are working with a string query:

Date today = Date.today();

List<Resource__c> resources = Database.query('Select Id, Name FROM Resource__c WHERE Status__c = \'Active\' and Start__c <= ' + today);

The proper way to use a variable in a SOQL date query is to preface it with a colon, which will give the successful result below:

Date today = Date.today();

List<Resource__c> resources = Database.query('Select Id, Name FROM Resource__c WHERE Status__c = \'Active\' and Start__c <= :today');

Not only will this yield results, but using this method will also clean up your queries a bit, so you don't end up overusing string concatenation. And this works for other variable types as well and isn’t just limited to dates.

If you’d like to learn more about the SOQL date comparison or how to build dynamic SOQL queries, contact the technology consultants at Wipfli. You can also keep reading more of our technology-focused articles here.