Date Formats and Date Literals in WHERE Clause in Salesforce

 A) Filter Query Results Using Dates

The fieldExpression of a WHERE clause supports filtering query results based on date and dateTime fields.

This table shows the supported date and dateTime formats that you can use in the WHERE clause of an SOQL query.

Field Type     Format                                                   Example

date               YYYY-MM-DD                                    1999-01-01

 

    

dateTime    YYYY-MM-DDThh:mm:ss+hh:mm         1999-01-01T23:01:01+01:00

                   YYYY-MM-DDThh:mm:ss-hh:mm          1999-01-01T23:01:01-08:00

                   YYYY-MM-DDThh:mm:ssZ                    1999-01-01T23:01:01Z

For example, this query filters for Account records that were created after the specified date and time.

SELECT Id

FROM Account

WHERE CreatedDate > 2005-10-08T01:02:03Z

B) Filter Query Results Using Date Literals

YESTERDAY:

SELECT Id FROM Account WHERE CreatedDate = YESTERDAY

TODAY:

SELECT Id FROM Account WHERE CreatedDate > TODAY

TOMORROW:   

SELECT Id FROM Opportunity WHERE CloseDate = TOMORROW

LAST_WEEK:

SELECT Id FROM Account WHERE CreatedDate > LAST_WEEK

THIS_WEEK:

SELECT Id FROM Account WHERE CreatedDate < THIS_WEEK

NEXT_WEEK:

SELECT Id FROM Opportunity WHERE CloseDate = NEXT_WEEK

LAST_MONTH:

SELECT Id FROM Opportunity WHERE CloseDate > LAST_MONTH

THIS_MONTH:

SELECT Id FROM Account WHERE CreatedDate < THIS_MONTH

NEXT_MONTH:

SELECT Id FROM Opportunity WHERE CloseDate = NEXT_MONTH

LAST_90_DAYS:

Starts with the current day and continues for the past 90 days.

This range includes the current day, not just previous days. So it includes 91 days in total.

SELECT Id FROM Account WHERE CreatedDate = LAST_90_DAYS

NEXT_90_DAYS:

Starts 00:00:00 UTC of the next day and continues for the next 90 days.

SELECT Id FROM Opportunity WHERE CloseDate > NEXT_90_DAYS    

LAST_N_DAYS:n

For the number n provided, starts with the current day and continues for the past n days.

This range includes the current day, not just previous days. For example, LAST_N_DAYS:1 includes yesterday and today.

SELECT Id FROM Account WHERE CreatedDate = LAST_N_DAYS:365

NEXT_N_DAYS:n

For the number n provided, starts 00:00:00 UTC of the next day and continues for the next n days.

This range doesn't include the current day. For example, NEXT_N_DAYS:1 is equivalent to TOMORROW.

SELECT Id FROM Opportunity WHERE CloseDate > NEXT_N_DAYS:15

Similarly other literals available are,

LAST_N_WEEKS:n  

NEXT_N_WEEKS:n

NEXT_N_MONTHS:n

LAST_N_MONTHS:n

THIS_QUARTER

LAST_QUARTER

NEXT_QUARTER

NEXT_N_QUARTERS:n

LAST_N_QUARTERS:n

THIS_YEAR

LAST_YEAR

NEXT_YEAR

LAST_N_YEARS:n

For more details visit,

https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_dateformats.htm