Date Comparisons in Dynamic SOQL Queries

Printer-friendly version

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 - in this example, 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\'');

Let's say that we also want to limit our resources by a date. We have a custom Date field on the Resource object <i>Start__c</i>, and we want to query only Active Resources with who have a Start__c value that is <= today. You might first try to do 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());

You'll get an error saying that when doing a date comparison you must actually be using a Date object, but obviously 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 query is to preface it with a colon, which will give us 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. Good luck!

About the Author:

TopLine Strategies delivers the complete integration and development of sales, marketing and customer service technologies that enable corporate clientele to improve revenue streams and strengthen customer interactions. Our project management and consulting is designed to achieve timely delivery, 100 percent user adoption of the technologies we implement and deliver measurable returns on investments for our clients.

Comments (0)

Related Blogs

TheReact Native Open Source roadmap was announced in Q4 2018 after they decided to invest more in the React Native open source community.

October is not just about pumpkins, fall foliage, and cooler temps anymore. October 2018 also means the exciting introduction of Microsoft Dynamics 365 for Customer Engagement.

Back in 2016, Microsoft introduced its intentions to refresh its CRM and ERP strategy with Dynamics 365. At the heart of its services was the Common Data Model (CDM).