Skip to content
JamForce

A Guide to Date Literals in SOQL

SOQL Date Literal Hero

In the world of Salesforce, managing and manipulating dates is a common requirement. Date literals in SOQL provide a convenient way to filter records based on common date ranges without needing to specify exact dates. This guide will delve into the various date literals available in SOQL and how to use them effectively.

What are Date Literals?

Section titled “What are Date Literals?”

Date literals are predefined date values that simplify the process of filtering records based on specific date ranges. They are particularly useful for creating dynamic queries that adjust based on the current date. Here are some commonly used date literals:

  • YESTERDAY — Starts at 12:00 AM on the day before the current day and continues for 24 hours.
  • TODAY — Starts at 12:00 AM on the current day and continues for 24 hours.
  • TOMORROW — Starts at 12:00 AM on the day after the current day and continues for 24 hours.
  • LAST_WEEK — Starts at 12:00 AM on the first day of the previous week and continues for 7 days.
  • THIS_WEEK — Starts at 12:00 AM on the first day of the current week and continues for 7 days.
  • NEXT_WEEK — Starts at 12:00 AM on the first day of the next week and continues for 7 days.
Visual to explain weeks
  • LAST_MONTH — Starts at 12:00 AM on the first day of the previous month and continues until the end of the last day of that month.
  • THIS_MONTH — Starts at 12:00 AM on the first day of the current month and continues until the end of the last day of that month.
  • NEXT_MONTH — Starts at 12:00 AM on the first day of the next month and continues until the end of the last day of that month.
  • LAST_90_DAYS — Starts at 12:00 AM 90 days before the current day and continues until the end of the previous day.
  • NEXT_90_DAYS — Starts at 12:00 AM on the current day and continues for the next 90 days.
Visual to show last/next 90 days
  • THIS_QUARTER — Starts at 12:00 AM on the first day of the current quarter and continues until the last day of that quarter.
  • LAST_QUARTER — Starts at 12:00 AM on the first day of the previous quarter and continues until the last day of that quarter.
  • NEXT_QUARTER — Starts at 12:00 AM on the first day of the next quarter and continues until the last day of that quarter.
  • THIS_FISCAL_QUARTER — Starts at 12:00 AM on the first day of the current fiscal quarter and continues until the last day of that quarter.
  • LAST_FISCAL_QUARTER — Starts at 12:00 AM on the first day of the previous fiscal quarter and continues until the last day of that quarter.
  • NEXT_FISCAL_QUARTER — Starts at 12:00 AM on the first day of the next fiscal quarter and continues until the last day of that quarter.
  • THIS_YEAR — Starts at 12:00 AM on the first day of the current year and continues until the last day of that year.
  • LAST_YEAR — Starts at 12:00 AM on the first day of the previous year and continues until the last day of that year.
  • NEXT_YEAR — Starts at 12:00 AM on the first day of the next year and continues until the last day of that year.
  • THIS_FISCAL_YEAR — Starts at 12:00 AM on the first day of the current fiscal year and continues until the last day of that year.
  • LAST_FISCAL_YEAR — Starts at 12:00 AM on the first day of the previous fiscal year and continues until the last day of that year.
  • NEXT_FISCAL_YEAR — Starts at 12:00 AM on the first day of the next fiscal year and continues until the last day of that year.

Dynamic Date Literals

Section titled “Dynamic Date Literals”

Dynamic date literals allow you to specify a range relative to the current date. These literals use a positive integer n to define the range.

  • LAST_N_DAYS:n — Starts at 12:00 AM n days before the current day and continues until the end of the previous day. For example, LAST_N_DAYS:10 represents the last 10 days, starting from 10 days ago and ending yesterday.
  • NEXT_N_DAYS:n — Starts at 12:00 AM on the current day and continues for the next n days. For example, NEXT_N_DAYS:10 represents the next 10 days, starting today and ending 10 days from now.
  • N_DAYS_AGO:n — Represents n days before the current day, starting at 12:00 AM and continuing for 24 hours. For example, N_DAYS_AGO:10 represents the day that was 10 days ago.
  • LAST_N_WEEKS:n — Starts at 12:00 AM on the first day of the week n weeks ago and continues for 7 days. For example, LAST_N_WEEKS:2 represents the last 2 weeks, starting from 2 weeks ago and ending at the end of the previous week.
  • NEXT_N_WEEKS:n — Starts at 12:00 AM on the first day of the current week and continues for the next n weeks. For example, NEXT_N_WEEKS:2 represents the next 2 weeks, starting this week and ending at the end of the second week from now.
Visual to show last/next N Weeks
  • N_WEEKS_AGO:n — Represents the week that was n weeks before the current week, starting at 12:00 AM on the first day of that week and continuing for 7 days. For example, N_WEEKS_AGO:2 represents the week that was 2 weeks ago.
  • LAST_N_MONTHS:n — Starts at 12:00 AM on the first day of the month n months ago and continues until the last day of that month. For example, LAST_N_MONTHS:3 represents the last 3 months, starting from 3 months ago and ending at the end of the previous month.
  • NEXT_N_MONTHS:n — Starts at 12:00 AM on the first day of the current month and continues for the next n months. For example, NEXT_N_MONTHS:3 represents the next 3 months, starting this month and ending at the end of the third month from now.
  • N_MONTHS_AGO:n — Represents the month that was n months before the current month, starting at 12:00 AM on the first day of that month and continuing until the last day of that month. For example, N_MONTHS_AGO:3 represents the month that was 3 months ago.
  • NEXT_N_QUARTERS:n — Starts at 12:00 AM on the first day of the current quarter and continues for the next n quarters. For example, NEXT_N_QUARTERS:2 represents the next 2 quarters, starting this quarter and ending at the end of the second quarter from now.
  • LAST_N_QUARTERS:n — Starts at 12:00 AM on the first day of the quarter n quarters ago and continues until the last day of that quarter. For example, LAST_N_QUARTERS:2 represents the last 2 quarters, starting from 2 quarters ago and ending at the end of the previous quarter.
  • N_QUARTERS_AGO:n — Represents the quarter that was n quarters before the current quarter, starting at 12:00 AM on the first day of that quarter and continuing until the last day of that quarter. For example, N_QUARTERS_AGO:2 represents the quarter that was 2 quarters ago.
  • NEXT_N_FISCAL_QUARTERS:n — Starts at 12:00 AM on the first day of the current fiscal quarter and continues for the next n fiscal quarters. For example, NEXT_N_FISCAL_QUARTERS:2 represents the next 2 fiscal quarters, starting this fiscal quarter and ending at the end of the second fiscal quarter from now.
  • LAST_N_FISCAL_QUARTERS:n — Starts at 12:00 AM on the first day of the fiscal quarter n fiscal quarters ago and continues until the last day of that fiscal quarter. For example, LAST_N_FISCAL_QUARTERS:2 represents the last 2 fiscal quarters, starting from 2 fiscal quarters ago and ending at the end of the previous fiscal quarter.
  • N_FISCAL_QUARTERS_AGO:n — Represents the fiscal quarter that was n fiscal quarters before the current fiscal quarter, starting at 12:00 AM on the first day of that fiscal quarter and continuing until the last day of that fiscal quarter. For example, N_FISCAL_QUARTERS_AGO:2 represents the fiscal quarter that was 2 fiscal quarters ago.
  • LAST_N_YEARS:n — Starts at 12:00 AM on the first day of the year n years ago and continues until the last day of that year. For example, LAST_N_YEARS:2 represents the last 2 years, starting from 2 years ago and ending at the end of the previous year.
  • NEXT_N_YEARS:n — Starts at 12:00 AM on the first day of the current year and continues for the next n years. For example, NEXT_N_YEARS:2 represents the next 2 years, starting this year and ending at the end of the second year from now.
  • NEXT_N_FISCAL_YEARS:n — Starts at 12:00 AM on the first day of the current fiscal year and continues for the next n fiscal years. For example, NEXT_N_FISCAL_YEARS:2 represents the next 2 fiscal years, starting this fiscal year and ending at the end of the second fiscal year from now.
  • LAST_N_FISCAL_YEARS:n — Starts at 12:00 AM on the first day of the fiscal year n fiscal years ago and continues until the last day of that fiscal year. For example, LAST_N_FISCAL_YEARS:2 represents the last 2 fiscal years, starting from 2 fiscal years ago and ending at the end of the previous fiscal year.
  • N_FISCAL_YEARS_AGO:n — Represents the fiscal year that was n fiscal years before the current fiscal year, starting at 12:00 AM on the first day of that fiscal year and continuing until the last day of that fiscal year. For example, N_FISCAL_YEARS_AGO:2 represents the fiscal year that was 2 fiscal years ago.

Examples of Using Date Literals

Section titled “Examples of Using Date Literals”
SELECT Id, Name, CreatedDate FROM Account WHERE CreatedDate = LAST_MONTH

This query returns all Account records created in the previous month e.g. if we were in August, this query returns all the Account records created in July.

SELECT Id, Name, LastModifiedDate FROM Account WHERE LastModifiedDate = LAST_N_DAYS:10

This query returns any Accounts modified in the last 10 days.

SELECT Id, Name, CloseDate FROM Opportunity WHERE CloseDate = NEXT_N_WEEKS:1

This query returns any Opportunity that is due to close in the following week so assuming today is Saturday 24th August and thedate range is Monday 26th August -> Sunday 1st September inclusive.

Tips for Using Date Literals

Section titled “Tips for Using Date Literals”
  • Understand the Context: Ensure you understand the context of the date literal you are using. For example, LAST_MONTH will return records from the entire previous month, not just the last 30 days.
  • Combine with Other Filters: Date literals can be combined with other filters to create more specific queries.
  • Test Your Queries: Always test your queries to ensure they return the expected results, especially when using dynamic date literals.

Conclusion

Section titled “Conclusion”

Mastering date literals in SOQL is essential for efficiently filtering and managing date-based records in Salesforce. By understanding and utilising these predefined date values, you can create dynamic and powerful queries that adapt to the current date context.

I hope this guide has provided you with a comprehensive understanding of date literals in SOQL.

For more information on date literals see the official Salesforce Documentation