Force.com SOQL and SOSL Reference
j
No Results
Search Tips:
- Please consider misspellings
- Try different search keywords
Newer Version Available
Date Formats and Date Literals
When you’re specifying dates in a SOQL query, the date can be either a specific date or
a date literal. SOQL defines many date literals such as YESTERDAY, TODAY, THIS_WEEK, LAST_MONTH,
NEXT_QUARTER, and THIS_FISCAL_YEAR.
A date literal is a fixed expression that represents a relative range of time such as last month, this week, or next year. Remember that dateTime field values are stored as Coordinated Universal Time (UTC). When one of these values is returned in Salesforce, it is automatically adjusted for the time zone specified in your organization preferences. SOQL queries return dateTime field values as UTC values. Your application may need to handle the conversion if you want to process these values in different time zones.
Date Formats
A fieldExpression uses different date formats for date or dateTime fields. If you specify a dateTime format in a query, you can filter on dateTime fields only. Similarly, if you specify a date format value, you can filter on date fields only:
| Format | Format Syntax | Example |
|---|---|---|
| Date only | YYYY-MM-DD | 1999-01-01 |
| Date, time, and time zone offset |
|
|
The zone offset is always from UTC. For more information, see:
Date Literals
A fieldExpression can use a date literal to compare a range of values to the value in a date or dateTime field. Each literal is a range of time beginning with midnight (12:00:00). To find a value within the range, use =. To find values on either side of the range, use > or <. The following table shows the available list of date literals, the ranges they represent, and examples:
| Date Literal | Range | Example |
|---|---|---|
| YESTERDAY | Starts 12:00:00 the day before and continues for 24 hours. | SELECT Id FROM Account WHERE CreatedDate = YESTERDAY |
| TODAY | Starts 12:00:00 of the current day and continues for 24 hours. | SELECT Id FROM Account WHERE CreatedDate > TODAY |
| TOMORROW | Starts 12:00:00 after the current day and continues for 24 hours. | SELECT Id FROM Opportunity WHERE CloseDate = TOMORROW |
| LAST_WEEK | Starts 12:00:00 on the first day of the week before the most recent first day of the week and continues for seven full days. First day of the week is determined by your locale. | SELECT Id FROM Account WHERE CreatedDate > LAST_WEEK |
| THIS_WEEK | Starts 12:00:00 on the most recent first day of the week before the current day and continues for seven full days. First day of the week is determined by your locale. | SELECT Id FROM Account WHERE CreatedDate < THIS_WEEK |
| NEXT_WEEK | Starts 12:00:00 on the most recent first day of the week after the current day and continues for seven full days. First day of the week is determined by your locale. | SELECT Id FROM Opportunity WHERE CloseDate = NEXT_WEEK |
| LAST_MONTH | Starts 12:00:00 on the first day of the month before the current day and continues for all the days of that month. | SELECT Id FROM Opportunity WHERE CloseDate > LAST_MONTH |
| THIS_MONTH | Starts 12:00:00 on the first day of the month that the current day is in and continues for all the days of that month. | SELECT Id FROM Account WHERE CreatedDate < THIS_MONTH |
| NEXT_MONTH | Starts 12:00:00 on the first day of the month after the month that the current day is in and continues for all the days of that month. | SELECT Id FROM Opportunity WHERE CloseDate = NEXT_MONTH |
| LAST_90_DAYS | Starts 12:00:00 of the current day and continues for the last 90 days. | SELECT Id FROM Account WHERE CreatedDate = LAST_90_DAYS |
| NEXT_90_DAYS | Starts 12:00:00 of the current 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 12:00:00 of the current day and continues for the last n days. | SELECT Id FROM Account WHERE CreatedDate = LAST_N_DAYS:365 |
| NEXT_N_DAYS:n | For the number n provided, starts 12:00:00 of the current day and continues for the next n days. | SELECT Id FROM Opportunity WHERE CloseDate > NEXT_N_DAYS:15 |
| NEXT_N_WEEKS:n | For the number n provided, starts 12:00:00 of the first day of the next week and continues for the next n weeks. | SELECT Id FROM Opportunity WHERE CloseDate > NEXT_N_WEEKS:4 |
| LAST_N_WEEKS:n | For the number n provided, starts 12:00:00 of the last day of the previous week and continues for the last n weeks. | SELECT Id FROM Account WHERE CreatedDate = LAST_N_WEEKS:52 |
| NEXT_N_MONTHS:n | For the number n provided, starts 12:00:00 of the first day of the next month and continues for the next n months. | SELECT Id FROM Opportunity WHERE CloseDate > NEXT_N_MONTHS:2 |
| LAST_N_MONTHS:n | For the number n provided, starts 12:00:00 of the last day of the previous month and continues for the last n months. | SELECT Id FROM Account WHERE CreatedDate = LAST_N_MONTHS:12 |
| THIS_QUARTER | Starts 12:00:00 of the current quarter and continues to the end of the current quarter. | SELECT Id FROM Account WHERE CreatedDate = THIS_QUARTER |
| LAST_QUARTER | Starts 12:00:00 of the previous quarter and continues to the end of that quarter. | SELECT Id FROM Account WHERE CreatedDate > LAST_QUARTER |
| NEXT_QUARTER | Starts 12:00:00 of the next quarter and continues to the end of that quarter. | SELECT Id FROM Account WHERE CreatedDate < NEXT_QUARTER |
| NEXT_N_QUARTERS:n | Starts 12:00:00 of the next quarter and continues to the end of the nth quarter. | SELECT Id FROM Account WHERE CreatedDate < NEXT_N_QUARTERS:2 |
| LAST_N_QUARTERS:n | Starts 12:00:00 of the previous quarter and continues to the end of the previous nth quarter. | SELECT Id FROM Account WHERE CreatedDate > LAST_N_QUARTERS:2 |
| THIS_YEAR | Starts 12:00:00 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 12:00:00 on January 1 of the previous year and continues through the end of December 31 of that year. | SELECT Id FROM Opportunity WHERE CloseDate > LAST_YEAR |
| NEXT_YEAR | Starts 12:00:00 on January 1 of the following 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 12:00:00 on January 1 of the following 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 12:00:00 on January 1 of the previous year and continues through the end of December 31 of the previous nth year. | SELECT Id FROM Opportunity WHERE CloseDate > LAST_N_YEARS:5 |
| THIS_FISCAL_QUARTER | Starts 12:00:00 on the first day of the current fiscal quarter and continues through the end of the last day of the fiscal quarter. The fiscal year is defined in the company profile under Setup at . | SELECT Id FROM Account WHERE CreatedDate = THIS_FISCAL_QUARTER |
| LAST_FISCAL_QUARTER | Starts 12:00:00 on the first day of the last fiscal quarter and continues through the end of the last day of that fiscal quarter. The fiscal year is defined in the company profile under Setup at . | SELECT Id FROM Account WHERE CreatedDate > LAST_FISCAL_QUARTER |
| NEXT_FISCAL_QUARTER | Starts 12:00:00 on the first day of the next fiscal quarter and continues through the end of the last day of that fiscal quarter. The fiscal year is defined in the company profile under Setup at . | SELECT Id FROM Account WHERE CreatedDate < NEXT_FISCAL_QUARTER |
| NEXT_N_FISCAL_QUARTERS:n | Starts 12:00:00 on the first day of the next fiscal quarter and continues through the end of the last day of the nth fiscal quarter. The fiscal year is defined in the company profile under Setup at. | SELECT Id FROM Account WHERE CreatedDate < NEXT_N_FISCAL_QUARTERS:6 |
| LAST_N_FISCAL_QUARTERS:n | Starts 12:00:00 on the first day of the last fiscal quarter and continues through the end of the last day of the previous nth fiscal quarter. The fiscal year is defined in the company profile under Setup at . | SELECT Id FROM Account WHERE CreatedDate > LAST_N_FISCAL_QUARTERS:6 |
| THIS_FISCAL_YEAR | Starts 12:00:00 on the first day of the current fiscal year and continues through the end of the last day of the fiscal year. The fiscal year is defined in the company profile under Setup at . | SELECT Id FROM Opportunity WHERE CloseDate = THIS_FISCAL_YEAR |
| LAST_FISCAL_YEAR | Starts 12:00:00 on the first day of the last fiscal year and continues through the end of the last day of that fiscal year. The fiscal year is defined in the company profile under Setup at . | SELECT Id FROM Opportunity WHERE CloseDate > LAST_FISCAL_YEAR |
| NEXT_FISCAL_YEAR | Starts 12:00:00 on the first day of the next fiscal year and continues through the end of the last day of that fiscal year. The fiscal year is defined in the company profile under Setup at . | SELECT Id FROM Opportunity WHERE CloseDate < NEXT_FISCAL_YEAR |
| NEXT_N_FISCAL_YEARS:n | Starts 12:00:00 on the first day of the next fiscal year and continues through the end of the last day of the nth fiscal year. The fiscal year is defined in the company profile under Setup at . | SELECT Id FROM Opportunity WHERE CloseDate < NEXT_N_FISCAL_YEARS:3 |
| LAST_N_FISCAL_YEARS:n | Starts 12:00:00 on the first day of the last fiscal year and continues through the end of the last day of the previous nth fiscal year. The fiscal year is defined in the company profile under Setup at . | SELECT Id FROM Opportunity WHERE CloseDate > LAST_N_FISCAL_YEARS:3 |
Minimum and Maximum Dates
Only dates within a certain range are valid. The earliest valid date is 1700-01-01T00:00:00Z GMT, or just after midnight on January 1, 1700. The latest valid date is 4000-12-31T00:00:00Z GMT, or just after midnight on December 31, 4000. These values are offset by your time zone. For example, in the Pacific time zone, the earliest valid date is 1699-12-31T16:00:00, or 4:00 PM on December 31, 1699.