6
SOQL Date Literals In Salesforce
Chapter Topics
- SOQL Basics (Salesforce Object Query Language)
- How to Write SOQL in APEX
- SOQL Variable Binding in APEX
- SOQL Keywords
- SOQL Date Literals In Salesforce
- SOQL Aggregate Query
- Child to Parent Relationship
- Parent to Child – Relationship Queries in SOQL
- SOQL Multi level Relationships
- SOQL Return Type
- Salesforce Dynamic SOQL
- SOQL ’for’ Loops
A SOQL Date Literal in Salesforce is a fixed expression representing a relative time range, such as last month, this week, or next year. Whenever we write SOQL date literals, it substitutes a value that should be there and which is dynamic. These can be used in SOQL filters to receive relevant results based on your specific query.
SOQL Date Format
If You wish to query your results according to a specific date, you can set a value using SOQL date format. Whereas, for a result based on date and time, set a value using a dateTime format.
| 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 |
SOQL Date Functions in Salesforce
There are various SOQL date functions available in Salesforce for applying relevant date functions. The table below lists all the SOQL date literals and their range:
| Date Literal | Range | Example |
|---|---|---|
| YESTERDAY | Starts at 12:00:00 AM on the day before the current day and continues for 24 hours. | SELECT Id FROM Account WHERE CreatedDate = YESTERDAY |
| TODAY | Starts at 12:00:00 AM on the current day and continues for 24 hours. | SELECT Id FROM Account WHERE CreatedDate < TODAY |
| TOMORROW | Starts at 12:00:00 AM. on the day after the current day and continues for 24 hours. | SELECT Id FROM Opportunity WHERE CloseDate = TOMORROW |
| LAST_WEEK | Starts at 12:00:00 AM on the first day of the week before the current week and continues for seven days. | SELECT Id FROM Account WHERE CreatedDate > LAST_WEEK |
| THIS_WEEK | Starts at 12:00:00 AM on the first day of the current week and continues for seven days. | SELECT Id FROM Account WHERE CreatedDate < THIS_WEEK |
| NEXT_WEEK | Starts at 12:00:00 AM on the first day of the week after the current week and continues for seven days. | SELECT Id FROM Opportunity WHERE CloseDate = NEXT_WEEK |
| LAST_MONTH | Starts at 12:00:00 AM on the first day of the month before the current month and continues for all the days of that month. | SELECT Id FROM Opportunity WHERE CloseDate > LAST_MONTH |
| THIS_MONTH | Starts at 12:00:00 AM on the first day of the current month and continues for all the days of that month. | SELECT Id FROM Account WHERE CreatedDate < THIS_MONTH |
| NEXT_MONTH | Starts at 12:00:00 AM on the first day of the month after the current month and continues for all the days of that month. | SELECT Id FROM Opportunity WHERE CloseDate = NEXT_MONTH |
| LAST_90_DAYS | Starts at 12:00:00 AM 90 days before the current day and continues up to the current second. (The range includes today. Using this date value includes records from 91 days ago up to the current day.) | SELECT Id FROM Account WHERE CreatedDate = LAST_90_DAYS |
| NEXT_90_DAYS | Starts at 12:00:00 AM on the day that the report is run and continues for 90 days. (The range includes today.) | SELECT Id FROM Opportunity WHERE CloseDate > NEXT_90_DAYS |
| LAST_N_DAYS:n | Starts at 12:00:00 AM n days before the current day and continues up to the current second. (The range includes today. Using this date value includes records from n + 1 days ago up to the current day.) In standard filters, n can be 7, 30, 60, 90, or 120. | SELECT Id FROM Account WHERE CreatedDate = LAST_N_DAYS:365 |
| NEXT_N_DAYS:n | For standard date filters, starts at 12:00:00 AM on the day that the report is run and continues for n days. (The range includes today.) In standard filters, n can be 7, 30, 60, 90, or 120.For custom field filters, it starts at 12:00:00 AM on the next day and continues for the next n days. (The range doesn’t include today.) | SELECT Id FROM Opportunity WHERE CloseDate > NEXT_N_DAYS:15 |
| N_DAYS_AGO:n | Starts at 12:00:00 AM on the day n days before the current day and continues for 24 hours. (The range doesn’t include today.) | SELECT Id FROM Opportunity WHERE CloseDate = N_DAYS_AGO:25 |
| NEXT_N_WEEKS:n | Starts at 12:00:00 AM on the first day of the week after the current week and continues for n times seven days. | SELECT Id FROM Opportunity WHERE CloseDate > NEXT_N_WEEKS:4 |
| LAST_N_WEEKS:n | Starts at 12:00:00 AM on the first day of the week that started n weeks before the current week, and continues up to 11:59 PM on the last day of the week before the current week. | SELECT Id FROM Account WHERE CreatedDate = LAST_N_WEEKS:52 |
| N_WEEKS_AGO:n | Starts at 12:00:00 AM on the first day of the week that started n weeks before the start of the current week and continues for seven days. | SELECT Id FROM Opportunity WHERE CloseDate = N_WEEKS_AGO:3 |
| NEXT_N_MONTHS:n | Starts at 12:00:00 AM on the first day of the month after the current month and continues until the end of the nth month. | SELECT Id FROM Opportunity WHERE CloseDate > NEXT_N_MONTHS:2 |
| LAST_N_MONTHS:n | Starts at 12:00:00 AM on the first day of the month that started n months before the current month and continues up to 11:59 PM on the last day of the month before the current month. | SELECT Id FROM Account WHERE CreatedDate = LAST_N_MONTHS:12 |
| N_MONTHS_AGO:n | Starts at 12:00:00 AM on the first day of the month that started n months before the start of the current month and continues for all the days of that month. | SELECT Id FROM Opportunity WHERE CloseDate = N_MONTHS_AGO:6 |
| THIS_QUARTER | Starts at 12:00:00 AM on the first day of the current calendar quarter and continues to the end of the quarter. | SELECT Id FROM Account WHERE CreatedDate = THIS_QUARTER |
| LAST_QUARTER | Starts at 12:00:00 AM on the first day of the calendar quarter before the current calendar quarter and continues to the end of that quarter. | SELECT Id FROM Account WHERE CreatedDate = LAST_QUARTER |
| NEXT_QUARTER | Starts at 12:00:00 AM on the first day of the calendar quarter after the current calendar quarter and continues to the end of that quarter. | SELECT Id FROM Account WHERE CreatedDate < NEXT_QUARTER |
| NEXT_N_QUARTERS:n | Starts at 12:00:00 AM on the first day of the calendar quarter after the current quarter and continues to the end of the calendar quarter n quarters in the future. (The range doesn’t include the current quarter.) | SELECT Id FROM Account WHERE CreatedDate < NEXT_N_QUARTERS:2 |
| LAST_N_QUARTERS:n | Starts at 12:00:00 AM on the first day of the calendar quarter n quarters ago and continues to the end of the calendar quarter before the current quarter. (The range doesn’t include the current quarter.) | SELECT Id FROM Account WHERE CreatedDate = LAST_N_QUARTERS:2 |
| N_QUARTERS_AGO:n | Starts at 12:00:00 AM on the first day of the calendar quarter n quarters before the current calendar quarter and continues to the end of that quarter. | SELECT Id FROM Opportunity WHERE CloseDate = N_QUARTERS_AGO:3 |
| THIS_YEAR | Starts at 12:00:00 AM on January 1 of the current year and continues through the end of December 31 of the current year. | SELECT Id FROM Opportunity WHERE CloseDate = THIS_YEAR |
| LAST_YEAR | Starts at 12:00:00 AM on January 1 of the year before the current year and continues through the end of December 31 of that year. | SELECT Id FROM Opportunity WHERE CloseDate > LAST_YEAR |
| NEXT_YEAR | Starts at 12:00:00 AM on January 1 of the year after the current year and continues through the end of December 31 of that year. | SELECT Id FROM Opportunity WHERE CloseDate < NEXT_YEAR |
| NEXT_N_YEARS:n | Starts at 12:00:00 AM on January 1 of the year after the current year and continues through the end of December 31 of the nth year. | SELECT Id FROM Opportunity WHERE CloseDate = NEXT_N_YEARS:5 |
| LAST_N_YEARS:n | Starts at 12:00:00 am on January 1, n+1 years ago. The range ends on December 31 of the year before the current year. | SELECT Id FROM Opportunity WHERE CloseDate = LAST_N_YEARS:5 |
| N_YEARS_AGO:n | Starts at 12:00:00 AM on January 1 of the calendar year n years before the current calendar year and continues through the end of December 31 of that year. | SELECT Id FROM Opportunity WHERE CloseDate = N_YEARS_AGO:2 |
| THIS_FISCAL_QUARTER | Starts at 12:00:00 AM on the first day of the current fiscal quarter and continues through the end of the last day of the current fiscal quarter. The fiscal quarter is defined on the Fiscal Year page in Setup. | SELECT Id FROM Account WHERE CreatedDate = THIS_FISCAL_QUARTER |
| LAST_FISCAL_QUARTER | Starts at 12:00:00 AM on the first day of the fiscal quarter before the current fiscal quarter and continues through the last day of that fiscal quarter. The fiscal quarter is defined on the Fiscal Year page in Setup. | SELECT Id FROM Account WHERE CreatedDate > LAST_FISCAL_QUARTER |
| NEXT_FISCAL_QUARTER | Starts at 12:00:00 AM on the first day of the fiscal quarter after the current fiscal quarter and continues through the last day of that fiscal quarter. (The range doesn’t include the current quarter.) The fiscal quarter is defined on the Fiscal Year page in Setup. | SELECT Id FROM Account WHERE CreatedDate < NEXT_FISCAL_QUARTER |
| NEXT_N_FISCAL_QUARTERS:n | Starts at 12:00:00 AM on the first day of the fiscal quarter after the current fiscal quarter and continues through the end of the last day of the nth fiscal quarter. (The range doesn’t include the current fiscal quarter.) The fiscal quarter is defined on the Fiscal Year page in Setup. | SELECT Id FROM Account WHERE CreatedDate = NEXT_N_FISCAL_QUARTERS:6 |
| LAST_N_FISCAL_QUARTERS:n | Starts at 12:00:00 AM on the first day of the fiscal quarter n fiscal quarters ago and continues through the end of the last day of the previous fiscal quarter. (The range doesn’t include the current fiscal quarter.) The fiscal quarter is defined on the Fiscal Year page in Setup. | SELECT Id FROM Account WHERE CreatedDate > LAST_N_FISCAL_QUARTERS:6 |
| N_FISCAL_QUARTERS_AGO:n | Starts at 12:00:00 AM on the first day of the fiscal quarter n fiscal quarters before the current fiscal quarter and continues through the end of the last day of that fiscal quarter. | SELECT Id FROM Opportunity WHERE CloseDate = N_FISCAL_QUARTERS_AGO:6 |
| THIS_FISCAL_YEAR | Starts at 12:00:00 AM on the first day of the current fiscal year and continues through the end of the last day of the fiscal year. The fiscal quarter is defined on the Fiscal Year page in Setup. | SELECT Id FROM Opportunity WHERE CloseDate = THIS_FISCAL_YEAR |
| LAST_FISCAL_YEAR | Starts at 12:00:00 AM on the first day of the fiscal year before the current fiscal year and continues through the end of the last day of that fiscal year. The fiscal quarter is defined on the Fiscal Year page in Setup. | SELECT Id FROM Opportunity WHERE CloseDate > LAST_FISCAL_YEAR |
| NEXT_FISCAL_YEAR | Starts at 12:00:00 AM on the first day of the fiscal year after the current fiscal year and continues through the end of the last day of that fiscal year. The fiscal quarter is defined on the Fiscal Year page in Setup. | SELECT Id FROM Opportunity WHERE CloseDate < NEXT_FISCAL_YEAR |
| NEXT_N_FISCAL_YEARS:n | Starts at 12:00:00 AM on the first day of the fiscal year after the current fiscal year and continues through the end of the last day of the nth fiscal year. (The range doesn’t include the current fiscal year.) The fiscal quarter is defined on the Fiscal Year page in Setup. | SELECT Id FROM Opportunity WHERE CloseDate = NEXT_N_FISCAL_YEARS:3 |
| LAST_N_FISCAL_YEARS:n | Starts at 12:00:00 AM on the first day of the fiscal year n fiscal years ago and continues through the end of the last day of the fiscal year before the current fiscal year. (The range doesn’t include the current fiscal year.) The fiscal quarter is defined on the Fiscal Year page in Setup. | SELECT Id FROM Opportunity WHERE CloseDate = LAST_N_FISCAL_YEARS:3 |
| N_FISCAL_YEARS_AGO:n | Starts at 12:00:00 AM on the first day of the fiscal year n fiscal years ago and continues through the end of the last day of that fiscal year. | SELECT Id FROM Opportunity WHERE CloseDate = N_FISCAL_YEARS_AGO:3 |
Need more support?
Get a head start with our FREE study notes!
Learn more and get all the answers you need at zero cost. Improve your skills using our detailed notes prepared by industry experts to help you excel.
