DATE Clause in SOQL (Salesforce Object Query Language)

When you have many records in the Salesforce database from any standard or custom objects, you want to fetch records in a specific range of dates using an SOQL query in Apex. For that, in SOQL, we can use a date clause and different date literals to get the query result for a specific date or range.

In this Salesforce SOQL tutorial, we will learn about the DATE clause in SOQL (Salesforce Object Query Language). In that, I will explain the functionality of the Date clause and literals and how to use it in SOQL queries with different examples.

Date Clause in SOQL

The Date clause in SOQL (Salesforce Object Query Language) is used to filter query results based on date and time fields. These clauses are very useful when working with date and time data in Salesforce. Using this clause, we can fetch records filtering using specific date values, ranges, or Salesforce’s predefined date literals.

When using the DATE clause, the DATE fields must be formatted in the YYYY-MM-DD format. For DATETIME fields, use the YYYY-MM-DDTHH:MM:SSZ format (UTC).

In the DATETIME format, T is a separator between date and time. Z indicates that the time is in UTC (Coordinated Universal Time).

Syntax of DATE Clause in SOQL:

SELECT fields FROM Object WHERE Date_Field Operator Date_or_DateTime_Value / Date_Literals

In the SELECT clause, we provide the fields we want to fetch in the result. The FROM clause specifies from which standard or custom object we are retrieving data. We want to display data based on a date, so we need to provide the Date_Field from the selected object.

Operator and Date_Literals I have given a detailed explanation below.

Date Literals in SOQL Query

In Salesforce Object Query Language(SOQL), date literals are predefined date values that we can use in queries to filter records based on relative or fixed date ranges. These literals simplify writing date-based queries by avoiding the need to calculate exact dates manually.

There are two types of date literals:

• Relative Date Literals: Represent time frames relative to the current date.
• Absolute Date Literals: Represent fixed date values.

In the table below, I have explained some date literals in the DATE clause:

For more information, visit the official Salesforce website: Date Literals in SOQL.

Operators in SOQL Query

In the above SOQL DATE clause syntax, using operators in the DATE clause in SOQL is essential to filter records based on date and time fields. Operators like =, <, >, and BETWEEN let us define exact conditions for date-related queries, such as finding records created before a specific date or within a range.

When we add conditions to the DATE clause, it narrows the query scope, reducing the retrieved data result and improving performance.

In the below table, I have explained the operator and its uses in the DATE clause:

Example: Use Specific Date in SOQL DATE Clause

For example, we want to retrieve all opportunity records whose opportunity close date is 2024-11-21.

SELECT Name, CloseDate FROM Opportunity WHERE CloseDate = 2024-11-21

In the above SOQL query, we fetched opportunity records whose close date is 2024-11-21. We used the WHERE clause to add a condition to find only records with that particular date.

In this way, we can retrieve specific date records using the date clause in SOQL.

Example: Use Comparison Operator in SOQL DATE Clause

We have explored various types of comparison operators and their applications. By using a comparison operator in the SOQL DATE clause, we want to retrieve account records that were created within a specific time range.

SELECT Id, Name FROM Account WHERE CreatedDate >= 2024-05-01T00:00:00Z AND CreatedDate <= 2024-11-21T00:00:00Z 

In the above SOQL query, we are trying to fetch the records between two Created Dates. For that, we used the comparison and AND operator to get records within a specific time range. While adding data, we also included the time because the CreatedDate field is a Date/Time field, so we must provide the time as well.

In this way, we can use the comparison operator in the SOQL DATE clause to get the records between two specific dates.

Example: Use Date Literals in SOQL Query

For example, we want to retrieve opportunity records that were closed in the previous month from the current month. To retrieve records from the previous month, we use date literals, which allow us to filter records based on relative or fixed date ranges.

SELECT Id, Name, CloseDate FROM Opportunity WHERE CloseDate = LAST_MONTH 

In the above SOQL query, we fetched the opportunity records that were closed last month.

Use Relative Date For N Number of Days in SOQL

For example, we want to retrieve opportunity records that will be closed after 20 days. For that, we need to use the NEXT_N_DAYS literal to get the N number of days after the records.

SELECT Name, CloseDate FROM Opportunity WHERE CloseDate = NEXT_N_DAYS : 20

So, when we want to retrieve data from the next few days, we can use the NEXT_N_DAYS literal. In the above SOQL query, we fetched records whose close dates will be in the next 20 days.

In this way, we can use the relative date and absolute date literals to retrieve data from the Salesforce database in SOQL.

Example: Use the IN Operator in the DATE Clause SOQL

In SOQL, the IN operator can be used with the DATE field to filter records based on a range or a specific set of dates. When we use the IN operator with a WHERE clause with date fields, it allows us to specify a collection of dates that we want to match.

Syntax:

SELECT fields FROM Object_Name WHERE date_field IN (date_value1, date_value2, ...)

Using the WHERE clause, we added the condition for which records we want to fetch. Then, using the IN operator from the date field, we provide the date values so that we can retrieve records of those particular dates or a range of dates.

For example, we want to retrieve the leads records, which were updated last time, for the following specific dates:

• 2024-07-31, 2024-08-20, 2024-09-27. These are dates, and we want to retrieve lead records that are updated on these dates.

But when you provide these dates in the SOQL query, you will get an error because the LastModifiedDate field is a DateTime field, and we only provide the date. So first, we need to get the value of LastModifiedDate in DateTime format.

SELECT LastModifiedDate FROM Lead

From the above result, we need to provide date values in YYYY-MM-DDTHH:MM:SSZ format (UTC).

So, let’s understand how to query to get the records of specific days using the DATE-TIME value.

SELECT Name, LastModifiedDate FROM Lead WHERE LastModifiedDate IN (2024-07-31T07:34:25.00Z, 2024-08-20T08:01:45.00Z, 2024-09-27T06:26:53.00Z) 

In the above SOQL query, we fetched the last modified records of particular dates from leads. To get the records of specific dates, we need to use the IN operator, and we can provide multiple dates.

In this way, we can use the IN operator to retrieve data on specific dates from the Salesforce database in SOQL.

Use IN Operator in Date Literals

Now, we will see how to use the IN operator in date literals. For example, we want to fetch the opportunity records that were closed last month. So here, we will not use any date value; instead of, we can use the LAST_MONTH date literal.

SELECT Name, CloseDate FROM Opportunity WHERE CloseDate IN (LAST_MONTH)

In this way, we can use the IN operator to retrieve data representing time frames relative to the current date from the Salesforce database in SOQL.

Example: Use BETWEEN Operator on Date Literals in SOQL

When we want to retrieve records from the Salesforce database between two date literals, it does not support date literals directly; it is only used for specific ranges with explicit values.

Below, I have explained how to retrieve records between two date literals, like LAST_MONTH and THIS_MONTH.

SELECT Id, Name, CloseDate FROM Opportunity WHERE CloseDate >= LAST_MONTH AND CloseDate <= THIS_MONTH

Using the above SOQL query, we fetched opportunity records that were closed between the current date of the current month and the previous month. But we didn’t use the BETWEEN operator because we cannot use it with date literals.

Instead, we used comparison operators, so for the last month, we used a close date greater than or equal to, which means we retrieved records whose close date is greater than 1st day of the last month.

Then, for this month, we used less than or equal to because we retrieved records that were closed before the current date.

Example: Use DAY_ONLY Function on Date in SOQL

In Salesforce Object Query Language (SOQL), the DAY_ONLY function is used to extract just the date portion from a DateTime field. It converts a DateTime value into a Date value, which can then be compared to other Date fields or literals. This is particularly useful when you want to query records based on the date without worrying about the specific time.

Syntax:

SELECT fields FROM object WHERE DAY_ONLY(DateTimeField) = Date_Value / Literals

In the above syntax, the DAY_ONLY function works in DateTime fields. It extracts the date value from the date and time value so that we don’t need to provide the entire date and time value in the SOQL query. We can compare the results with another date field or a date literal.

For example, we want to retrieve the account records that were last updated on August 2, 2024. But we know the LastModifiedDate field is the DateTime field, so when we pass the value in SOQL query in YYYY-MM-DDTHH:MM:SSZ format, and for that, we also need the exact time when the record is updated.

To make it simple, we have a DAY_ONLY function where we can enter only the date value instead of the DateTime value in the SOQL query.

SELECT Id, Name, LastModifiedDate FROM Account WHERE DAY_ONLY(LastModifiedDate) = 2024-08-02

Use DAY_ONLY Function on Date Literals in SOQL

Now, instead of providing a date value in the SOQL query, let’s use date literals to retrieve the records based on relative or fixed date ranges.

For example, we want to retrieve account records that were created in the previous month from the current month. To retrieve records from the previous month, we use date literals, which allow us to filter records based on relative or fixed date ranges.

SELECT Id, Name, CreatedDate FROM Account WHERE DAY_ONLY(CreatedDate) = LAST_MONTH

In the above SOQL query, we fetched the account records that were created last month.

In this way, we can use the DAY_ONLY function in the date clause SOQL query to extract the day value from the DateTime value.

Conclusion

I hope you have got an idea about the DATE clause in SOQL (Salesforce Object Query Language). In that, I have explained the date literals and operators that we can use in SOQL queries, the functionality of the date clause and literals, and how to use them in SOQL queries with different examples.

You may like to read:

• Salesforce Object Query Language (SOQL) in Apex
• Dynamic Queries in SOQL (Salesforce Object Query Language)
• GROUP BY Clause in SOQL

Bijay Kumar

I am Bijay Kumar, the founder of SalesforceFAQs.com. Having over 10 years of experience working in salesforce technologies for clients across the world (Canada, Australia, United States, United Kingdom, New Zealand, etc.). I am a certified salesforce administrator and expert with experience in developing salesforce applications and projects. My goal is to make it easy for people to learn and use salesforce technologies by providing simple and easy-to-understand solutions. Check out the complete profile on About us.