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
Condition Expression Syntax (WHERE Clause)
fieldExpression Syntax
Comparison Operators
Logical Operators
Date Formats and Date Literals
USING SCOPE
ORDER BY
LIMIT
OFFSET
FIELDS()
Update an Article’s Keyword Tracking with SOQL
Update an Article Viewstat with SOQL
WITH filteringExpression
GROUP BY
HAVING
TYPEOF
FORMAT ()
FOR VIEW
FOR REFERENCE
FOR UPDATE
Aggregate Functions
Date Functions
Querying Currency Fields in Multi-Currency Orgs
Example SELECT Clauses
Relationship Queries
Change the Batch Size in Queries
SOQL Limits on Objects
Syndication Feed SOQL and Mapping Syntax
Location-Based SOQL Queries
Async SOQL
Salesforce Object Search Language (SOSL)
PDF

Newer Version Available

This content describes an older version of this product. View Latest

Date Formats and Date Literals

In a SOQL query, you can specify either a particular date or a date literal. A date literal is a fixed expression that represents a relative range of time, such as last month, this week, or next year.

dateTime field values are stored as Coordinated Universal Time (UTC). When a dateTime value is returned in Salesforce, it’s adjusted for the time zone specified in your org preferences. SOQL queries, however, return dateTime field values as UTC values. If you want to process these values in different time zones, your application might need to handle the conversion.

Date Formats

A fieldExpression uses different date formats for date and dateTime fields. If you specify a dateTime format in a query, you can filter only on dateTime fields. Similarly, if you specify a date format value, you can filter only on date fields.

Format Format Syntax Example
Date only YYYY-MM-DD 1999-01-01
Date, time, and time zone offset
  • 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

The zone offset is always from UTC. For more information, see:

For a fieldExpression that uses date formats, the date is not enclosed in single quotes. Don’t use quotes around the date. For example:

Note

The SELECT clause supports formatting of standard and custom number, date, time, and currency fields. These fields reflect the appropriate format for the given user locale. The field format matches what appears in the Salesforce Classic user interface.

Note

If you’ve defined custom fiscal years in the Salesforce user interface and in any FISCAL date literals that you specify a range that is outside the years you’ve defined, an invalid date error is returned.

Note

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 (00: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 00:00:00 the day before and continues for 24 hours. SELECT Id FROM Account WHERE CreatedDate = YESTERDAY
TODAY Starts 00:00:00 of the current day and continues for 24 hours. SELECT Id FROM Account WHERE CreatedDate > TODAY
TOMORROW Starts 00:00:00 after the current day and continues for 24 hours. SELECT Id FROM Opportunity WHERE CloseDate = TOMORROW
LAST_WEEK Starts 00:00:00 on the first day of the week before the most recent first day of the week and continues for seven full days. Your locale determines the first day of the week. SELECT Id FROM Account WHERE CreatedDate > LAST_WEEK
THIS_WEEK Starts 00:00:00 on the most recent first day of the week on or before the current day and continues for seven full days. Your locale determines the first day of the week. SELECT Id FROM Account WHERE CreatedDate < THIS_WEEK
NEXT_WEEK Starts 00:00:00 on the most recent first day of the week after the current day and continues for seven full days. Your locale determines the first day of the week. SELECT Id FROM Opportunity WHERE CloseDate = NEXT_WEEK
LAST_MONTH Starts 00: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 00: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 00: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 with the current day and continues for the past 90 days.

This 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 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 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 of the next day and continues for the next n days.

This does not include the current day. For example, NEXT_N_DAYS:1 is equivalent to TOMORROW.

 SELECT Id FROM Opportunity WHERE CloseDate > NEXT_N_DAYS:15
NEXT_N_WEEKS:n For the number n provided, starts 00: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 00:00:00 of the last day of the previous week and continues for the past n weeks. SELECT Id FROM Account WHERE CreatedDate = LAST_N_WEEKS:52
NEXT_N_MONTHS:n For the number n provided, starts 00: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 00:00:00 of the last day of the previous month and continues for the past n months. SELECT Id FROM Account WHERE CreatedDate = LAST_N_MONTHS:12
THIS_QUARTER Starts 00: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 00: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 00: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 00: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 00: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 00: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 00: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 00: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 00: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 00: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 00: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 on the Fiscal Year page in Setup. SELECT Id FROM Account WHERE CreatedDate = THIS_FISCAL_QUARTER
LAST_FISCAL_QUARTER Starts 00: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 on the Fiscal Year page in Setup. SELECT Id FROM Account WHERE CreatedDate > LAST_FISCAL_QUARTER
NEXT_FISCAL_QUARTER Starts 00: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 on the Fiscal Year page in Setup. SELECT Id FROM Account WHERE CreatedDate < NEXT_FISCAL_QUARTER
NEXT_N_FISCAL_​QUARTERS:n Starts 00: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 on the Fiscal Year page in Setup. SELECT Id FROM Account WHERE CreatedDate < NEXT_N_FISCAL_QUARTERS:6
LAST_N_FISCAL_​QUARTERS:n Starts 00: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 on the Fiscal Year page in Setup. SELECT Id FROM Account WHERE CreatedDate > LAST_N_FISCAL_QUARTERS:6
THIS_FISCAL_YEAR Starts 00: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 on the Fiscal Year page in Setup. SELECT Id FROM Opportunity WHERE CloseDate = THIS_FISCAL_YEAR
LAST_FISCAL_YEAR Starts 00: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 on the Fiscal Year page in Setup. SELECT Id FROM Opportunity WHERE CloseDate > LAST_FISCAL_YEAR
NEXT_FISCAL_YEAR Starts 00: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 on the Fiscal Year page in Setup. SELECT Id FROM Opportunity WHERE CloseDate < NEXT_FISCAL_YEAR
NEXT_N_FISCAL_​YEARS:n Starts 00: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 on the Fiscal Year page in Setup. SELECT Id FROM Opportunity WHERE CloseDate < NEXT_N_FISCAL_YEARS:3
LAST_N_FISCAL_​YEARS:n Starts 00: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 on the Fiscal Year page in Setup. SELECT Id FROM Opportunity WHERE CloseDate > LAST_N_FISCAL_YEARS:3
  • If the UNIT in LAST_N_UNIT:n is any unit except DAYS, the date literal does not include the current day. So, for example, LAST_N_WEEKS:1 does not 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.

Note

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.

Related Information

For more information on locales and time zones, see these topics: