To filter records by date in SOQL (Salesforce Object Query Language), you can use the comparison operators such as “=”, “<“, “<=”, “>”, “>=”, and “!=”. However a common mistake we made is to enclose the date with quotes like the following query:
SELECT Id, Name FROM Account WHERE CreatedDate < '2023-09-03' LIMIT 10
As soon as you execute it, you get this error:
INVALID_FIELD:
SELECT Id, Name FROM Account WHERE CreatedDate < '2023-09-03' LIMIT
^
ERROR at Row:1:Column:36
value of filter criterion for field 'CreatedDate' must be of type dateTime and should not be enclosed in quotes
However if we remove the quotes we will get the same error
SELECT Id, Name FROM Account WHERE CreatedDate < 2023-09-03 LIMIT 10
To resolve the error “value of filter criterion for field ‘CreatedDate’ must be of type dateTime and should not be enclosed in quotes” in Salesforce, you need to make the following adjustments to your SOQL query:
- Remove the single quotes around the date value.
- Use the correct format for the date value by appending the time ‘T23:59:59Z’ , ‘T00:00:00Z ‘ or the hour you need at the end.
By following these steps, your query will correctly compare the ‘CreatedDate’ field to the specified date and time, without encountering the type mismatch error so the right query will be:
SELECT Id, Name FROM Account WHERE CreatedDate < 2023-09-03T23:59:59Z LIMIT 10
Another example:
SELECT Id, Name FROM Account WHERE CreatedDate < 2023-09-03T00:00:00Z
In the above example, we are querying the Account object and filtering records where the CreatedDate is less than September 3, 2023, at 00:00:00 (midnight) UTC. You can modify the date and time values according to your specific requirements. Ensure that the date and time format follows the YYYY-MM-DDThh:mm:ssZ pattern, where T separates date and time, and Z indicates the timezone as UTC. Remember, using the correct date and time format is essential in SOQL to accurately filter out records based on dates.