Introduction

Salesforce developers often need to build dynamic queries in their applications. This is particularly useful when working with filters or conditions that change based on user input or business logic. One common requirement is constructing queries that handle Date and Datetime values dynamically. This blog will guide beginner Salesforce developers through creating dynamic SOQL queries with Date and Datetime strings.

Understanding Dynamic Queries in Salesforce

Dynamic SOQL queries allow you to construct queries at runtime as strings. This can be achieved using Apex’s Database.query() method. Unlike static SOQL, dynamic queries are not precompiled and can include variables to make them flexible.

Example: Static SOQL

List<Account> accounts = [SELECT Id, Name FROM Account WHERE Industry = 'Technology'];

Example: Dynamic SOQL

String industry = 'Technology';
String query = 'SELECT Id, Name FROM Account WHERE Industry = \'' + industry + '\'';
List<Account> accounts = Database.query(query);

In Salesforce, handling Date and Datetime fields dynamically can be tricky. Here’s how to do it effectively:

1. Working with Date Fields

Suppose you want to filter records created today. You can use Salesforce’s predefined date literals like TODAY, YESTERDAY, and LAST_N_DAYS.

Static Example:

List<Opportunity> opportunities = [SELECT Id, Name FROM Opportunity WHERE CloseDate = TODAY];

Dynamic Example:

String dateLiteral = 'TODAY';
String query = 'SELECT Id, Name FROM Opportunity WHERE CloseDate = ' + dateLiteral;
List<Opportunity> opportunities = Database.query(query);

2. Constructing Custom Date Strings

For more complex scenarios, you might need to construct a date string manually. Use the Date class for this.

Integer y = Date.today().year();
Integer m = Date.today().month();
Integer d = Date.today().day();

Date startDate = Date.newInstance(y, m, d);
String startDateStr = String.valueOf(startDate);

Date endDate = Date.newInstance(y, m, d).addDays(7);
String endDateStr = String.valueOf(endDate);

String query = 'SELECT Id, Name FROM Opportunity WHERE CloseDate >= ' + startDateStr + ' AND CloseDate <= ' + endDateStr;
List<Opportunity> opportunities = Database.query(query);

3. Working with Datetime Fields

Datetime fields require precise formatting. Use the Datetime class to format values appropriately.

Example: Filter Records Within the Last 24 Hours

Datetime now = Datetime.now();
Datetime yesterday = now.addDays(-1);
String yesterdayDateStr = yesterday.format('yyyy-MM-dd\'T\'HH:mm:ss\'Z\'');
String query = 'SELECT Id FROM Task WHERE CreatedDate >= ' + yesterdayDateStr;
List<Task> tasks = Database.query(query);

Using ISO-8601 Format for Datetime:
Salesforce requires Datetime values in ISO-8601 format, e.g., 2025-06-15T00:00:00Z.

Best Practices for Dynamic SOQL Queries

1. Avoid SOQL Injection: Always sanitize user inputs when including variables in SOQL queries.

String userInput = 'Technology\' OR Name != NULL';
String safeInput = String.escapeSingleQuotes(userInput);
String query = 'SELECT Id FROM Account WHERE Industry = \'' + safeInput + '\'';

2. Use Bind Variables When Possible:
If your query conditions are simple, bind variables are safer and more efficient.

String industry = 'Technology';
List<Account> accounts = [SELECT Id, Name FROM Account WHERE Industry = :industry];

3. Handle Null Values Gracefully:
Include checks to ensure your variables are not null before appending them to the query string.

4. Leverage SOQL Date Literals:
Use predefined literals (LAST_N_DAYS:n, NEXT_N_MONTHS:n) whenever possible to simplify your queries.

5. Format Datetime Correctly:
Use the Datetime.format() method to ensure compatibility with Salesforce’s requirements.

Conclusion

Mastering dynamic SOQL queries in Salesforce is an essential skill for developers. By understanding how to construct query strings with Date and Datetime filters, you can build flexible applications. These applications are powerful and adapt to varying business requirements. Always prioritize security and performance while constructing queries to ensure your application remains robust and efficient.

Happy coding! 🚀