» » SOQL Date Literals and Date Formats - SOQL basic | Salesforce funda

SOQL Date Literals and Date Formats - SOQL basic | Salesforce funda

 When SOQL statements we should follow some date formats which are predefined in salesforce. You can specify date values or date literals in WHERE clauses to filter SOQL query results. Dates represent a specific day or time, while date literals represent a relative range of time, such as last month, this week, or next year.


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

       YYYY-MM-DDThh:mm:ss-hh:mm

        YYYY-MM-DDThh:mm:ssZ

       1999-01-01T23:01:01+01:00

        1999-01-01T23:01:01-08:00

       1999-01-01T23:01:01Z


There are different types of SOQL Date literals are present when querying the records using the date fields in the SOQL statements the date literals can be used.

Some of them listed here :-

  • YESTERDAY
  • TODAY
  • TOMORROW
  • LAST_WEEK
  • NEXT_WEEK
  • LAST_MONTH
  • THIS_MONTH
  • NEXT_MONTH
  • LAST_90_DAYS
  • NEXT_90_DAYS
  • LAST_N_DAYS:n
  • NEXT_N_DAYS:n
  • THIS_QUARTER
  • NEXT_QUARTER
  • NEXT_N_QUARTER:n
  • LAST_N_QUARTERS:n
  • THIS_YEAR.
  • LAST_YEAR
  • NEXT_YEAR
  • NEXT_N_YEARS:n
  • LAST_N_YEARS:n
  • THIS_FISCAL_QUARTER
  • LAST_FISCAL_QUARTER
  • NEXT_FISCAL_QUARTER
  • NEXT_N_FISCAL_QUARTERS:n
  • LAST_N_FISCAL_QUARTERS:n
  • THIS_FISCAL_YEAR
  • LAST_FISCAL_YEAR
  • NEXT_FISCAL_YEAR
  • NEXT_N_FISCAL_YEAR:n
  • LAST_N_FISCAL_YEARS:n
SOQL Syntax :- 


//TODAY
SELECT Id FROM Account WHERE CreatedDate > TODAY
//YESTERDAY
SELECT Id FROM Account WHERE CreatedDate = YESTERDAY
//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
SELECT Id FROM Account WHERE CreatedDate = LAST_90_DAYS
//NEXT_90_DAYS
SELECT Id FROM Opportunity WHERE CloseDate > NEXT_90_DAYS
//LAST_N_DAYS:n
SELECT Id FROM Account WHERE CreatedDate = LAST_N_DAYS:365
//NEXT_N_DAYS:n
SELECT Id FROM Opportunity WHERE CloseDate > NEXT_N_DAYS:15
//N_DAYS_AGO:n
SELECT Id FROM Opportunity WHERE CloseDate = N_DAYS_AGO:25
//NEXT_N_WEEKS:n
SELECT Id FROM Opportunity WHERE CloseDate > NEXT_N_WEEKS:4
//LAST_N_WEEKS:n
SELECT Id FROM Account WHERE CreatedDate = LAST_N_WEEKS:52
//N_WEEKS_AGO:n
SELECT Id FROM Opportunity WHERE CloseDate = N_WEEKS_AGO:3
//NEXT_N_MONTHS:n
SELECT Id FROM Opportunity WHERE CloseDate > NEXT_N_MONTHS:2
//LAST_N_MONTHS:n
SELECT Id FROM Account WHERE CreatedDate = LAST_N_MONTHS:12
//N_MONTHS_AGO:n
SELECT Id FROM Opportunity WHERE CloseDate = N_MONTHS_AGO:6
//THIS_QUARTER
SELECT Id FROM Account WHERE CreatedDate = THIS_QUARTER
//LAST_QUARTER
SELECT Id FROM Account WHERE CreatedDate = LAST_QUARTER
//NEXT_QUARTER
SELECT Id FROM Account WHERE CreatedDate < NEXT_QUARTER
//NEXT_N_QUARTERS:n
SELECT Id FROM Account WHERE CreatedDate < NEXT_N_QUARTERS:2
//LAST_N_QUARTERS:n
SELECT Id FROM Account WHERE CreatedDate = LAST_N_QUARTERS:2
//N_QUARTERS_AGO:n
SELECT Id FROM Opportunity WHERE CloseDate = N_QUARTERS_AGO:3
//THIS_YEAR
SELECT Id FROM Opportunity WHERE CloseDate = THIS_YEAR
//LAST_YEAR
SELECT Id FROM Opportunity WHERE CloseDate > LAST_YEAR
//NEXT_YEAR
SELECT Id FROM Opportunity WHERE CloseDate < NEXT_YEAR
//NEXT_N_YEARS:n
SELECT Id FROM Opportunity WHERE CloseDate = NEXT_N_YEARS:5
//LAST_N_YEARS:n
SELECT Id FROM Opportunity WHERE CloseDate = LAST_N_YEARS:5
//N_YEARS_AGO:n
SELECT Id FROM Opportunity WHERE CloseDate = N_YEARS_AGO:2
//THIS_FISCAL_QUARTER
SELECT Id FROM Account WHERE CreatedDate = THIS_FISCAL_QUARTER
//LAST_FISCAL_QUARTER
SELECT Id FROM Account WHERE CreatedDate > LAST_FISCAL_QUARTERv
//NEXT_FISCAL_QUARTER
SELECT Id FROM Account WHERE CreatedDate < NEXT_FISCAL_QUARTER
//NEXT_N_FISCAL_​QUARTERS:n
SELECT Id FROM Account WHERE CreatedDate = NEXT_N_FISCAL_QUARTERS:6
//LAST_N_FISCAL_​QUARTERS:n
SELECT Id FROM Account WHERE CreatedDate > LAST_N_FISCAL_QUARTERS:6
//N_FISCAL_QUARTERS_AGO:n
SELECT Id FROM Opportunity WHERE CloseDate = N_FISCAL_QUARTERS_AGO:6
//THIS_FISCAL_YEAR
SELECT Id FROM Opportunity WHERE CloseDate = THIS_FISCAL_YEAR
//LAST_FISCAL_YEAR
SELECT Id FROM Opportunity WHERE CloseDate > LAST_FISCAL_YEAR
//NEXT_FISCAL_YEAR
SELECT Id FROM Opportunity WHERE CloseDate < NEXT_FISCAL_YEAR
//NEXT_N_FISCAL_​YEARS:n
SELECT Id FROM Opportunity WHERE CloseDate = NEXT_N_FISCAL_YEARS:3
//LAST_N_FISCAL_​YEARS:n
SELECT Id FROM Opportunity WHERE CloseDate = LAST_N_FISCAL_YEARS:3
//N_FISCAL_YEARS_AGO:n
SELECT Id FROM Opportunity WHERE CloseDate = N_FISCAL_YEARS_AGO:3



To use FISCAL date literals, you must first define custom fiscal years in Salesforce. If you use FISCAL date literals and specify a time range outside of a defined fiscal year, an invalid date error is returned.

Whether the current day is included in the range of a date literal depends on which date literal you use.
  • If the UNIT in LAST_N_UNIT:n is any unit except DAYS, the date literal doesn't include the current day. So, for example, LAST_N_WEEKS:1 doesn't include today.
  • The two date literals LAST_N_DAYS:n and LAST_90_DAYS do include the current day. So, for example, LAST_N_DAYS:1 includes yesterday and today. And LAST_90_DAYS includes 91 days, not just 90.

we can use query between two dates like this :-

SOQL Syntax :-

//using direct date
Select Id, Name from Account Where CreatedDate >= 2020-01-01T00:00:00z and CreatedDate <= TODAY
//using Date literals
Select Id, Name from Account Where CreatedDate >= LAST_N_DAYS:90 and CreatedDate <= NEXT_N_DAYS:90
//using common query
Select o.Id, o.Name from Account o Where o.CreatedDate >= LAST_N_DAYS:90 and o.CreatedDate <= NEXT_N_DAYS:90
Feature :-

Labels: ,