SOQL and SOSL Reference

Introduction to SOQL and SOSL
Salesforce Object Query Language (SOQL)
Typographical Conventions in This Document
Quoted String Escape Sequences
Reserved Characters
Alias Notation
SOQL SELECT Syntax
SELECT
TYPEOF
USING SCOPE
WHERE
Comparison Operators
Logical Operators
Date Formats and Date Literals in WHERE
Filter on Polymorphic Relationship Fields
Query Multi-Select Picklists
Use null in WHERE in WHERE
WITH
GROUP BY
HAVING
ORDER BY
LIMIT
OFFSET
FOR VIEW and and FOR REFERENCE
UPDATE
SET OPTIONS
FOR UPDATE
SOQL SELECT Examples
SOQL SELECT Functions
Relationship Queries
Change the Batch Size in Queries
SOQL Object Limits and Limitations
Syndication Feed SOQL and Mapping Syntax
Location-Based SOQL Queries
Considerations for Querying Currency Data Using SOQL
Salesforce Object Search Language (SOSL)
PDF

Date Formats and Date Literals in WHERE

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.

For information on formatting the dates and times returned by Salesforce, see FORMAT () and convertTimezone().

Filter Query Results Using Dates

The fieldExpression of a WHERE clause supports filtering query results based on date and dateTime fields. Use comparison operators between a field name and a specified date value to filter for results that match the condition. For example, this query filters for Account records that were created after the specified date and time.

1SELECT Id
2FROM Account
3WHERE CreatedDate > 2005-10-08T01:02:03Z

To filter query results based on a specific date, set a value using a date format. To filter query results based on a specific date and time, set a value using a dateTime format.

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

Although dateTime field values are stored in Salesforce as Coordinated Universal Time (UTC), you can use the time zone offset to provide dateTime values in another timezone. The time zone offset is always from UTC.

For more information on dateTime formats and time zone offsets, see:

Filter Query Results Using Date Literals

You can use a date literal in the fieldExpression of a WHERE clause to filter query results relative to a range of dates. For example, you can filter for query results that were created within the last three months, or you can filter for results with deadlines after the next fiscal year.

Each date literal represents a range of time relative to the current day. The exact start and stop of the range depends on the date literal and the locale of the user sending the query. If the user hasn't set a personal locale, the range depends on the locale of the org. For more information, see Select Your Language, Locale, and Currency in Salesforce Help.

To filter for results within a range, use the = comparison operator. To filter for results on either side of a range, use the > or < comparison operators. The table shows the available list of date literals, the ranges they represent, and examples.

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 next day and continues for 90 days. The range doesn’t include 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 next day and continues for n days. The range doesn’t include today. In standard filters, n can be 7, 30, 60, 90, or 120.

 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.

None of the FISCAL literal date values are supported when creating mobile custom views.

Note

 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

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.
Diagram of what is included and not included with the various date literals.

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.