SOQL: A Guide to Date Functions

---

In Salesforce, managing and manipulating date and time data is a crucial aspect of data analysis and reporting. SOQL (Salesforce Object Query Language) offers a variety of powerful date functions that allow developers and administrators to extract, convert, and calculate date and time values efficiently. Whether you need to filter records based on specific date criteria, convert time zones, or perform date arithmetic, understanding these functions is essential for creating dynamic and insightful queries.

This guide will walk you through some of the most commonly used date functions in SOQL, providing practical examples and tips to help you leverage these tools effectively. By mastering these functions, you can enhance your ability to analyse date and time data, create more precise reports, and ultimately make better data-driven decisions.

Here are some of the most commonly used date functions, given a date/time of 2024-08-27T10:24:48.978+12:00 (Pacific/Auckland time zone):

• CALENDAR_YEAR(): Extracts the calendar year from a date and would produce 2024.
• CALENDAR_MONTH(): Extracts the calendar month from a date and would produce 8.
• DAY_IN_MONTH(): Extracts the day of the month from a date and would produce 27.
• DAY_IN_WEEK(): Extracts the day of the week from a date and would produce 3 (Tuesday, assuming 1 = Sunday).
• DAY_IN_YEAR(): Extracts the day of the year from a date and would produce 240.
• WEEK_IN_YEAR(): Extracts the week of the year from a date and would produce 35.
• HOUR_IN_DAY(): Extracts the hour from a date/time field and would produce 10.
• MINUTE_IN_HOUR(): Extracts the minute from a date/time field and would produce 24.

Example

SELECT Id, Name, CreatedDate
FROM Account
WHERE CALENDAR_YEAR(CreatedDate) = 2023
OR DAY_IN_WEEK(CreatedDate) = 4

The query retrieves records from the Account object where either the CreatedDate is in the year 2023 or the CreatedDate falls on a Wednesday (assuming DAY_IN_WEEK returns 4 for Wednesday).

Date and Time Zone Conversion

When working with date/time fields, it’s often necessary to convert dates to different time zones. SOQL queries in a client application return dateTime field values as Coordinated Universal Time (UTC) values. SOQL provides the convertTimezone function to convert dateTime fields to the user’s time zone.

SELECT HOUR_IN_DAY(convertTimezone(CreatedDate)), SUM(Amount)
FROM Opportunity
GROUP BY HOUR_IN_DAY(convertTimezone(CreatedDate))

This query groups opportunities by the hour of the day they were created, after converting the creation time to the user’s time zone. It then calculates the total amount for opportunities created within each hour.

Date Formats

Understanding the format of date and date/time fields in Salesforce is crucial for effective querying and data manipulation. Salesforce uses specific formats for dates and date/time values.

Example

SELECT Id, Name, CreatedDate FROM Account WHERE CreatedDate = 2024-08-27

This query retrieves records from the Account object where the CreatedDate is exactly 27th August 2024.

Date Calculations

While in SQL you can perform date calculations using arithmetic operations, in SOQL it is not so straight forward; you cannot directly add or subtract days using arithmetic operations like TODAY - 30. Instead, you should use date literals or functions designed for date manipulation.

Example:

SELECT Id, Name, CloseDate
FROM Opportunity
WHERE CloseDate = LAST_N_DAYS:30
AND CloseDate < LAST_N_DAYS:29

In this example, assuming today’s date is 27th August 2024, the query will return opportunities with a CloseDate of 28th July 2024 (30 days ago). It does this by applying two filters:

• First it filters the opportunities to include all those with a CloseDate that falls within 30 days before the current date.
• It further filters the opportunities to exclude those with a CloseDate that falls 29 days or less before the current date.

Date Grouping

Grouping data by date can provide valuable insights into trends and patterns over time.

• Group by Calendar Year and Month

SELECT CALENDAR_YEAR(CreatedDate), CALENDAR_MONTH(CreatedDate), COUNT()
FROM Opportunity
GROUP BY CALENDAR_YEAR(CreatedDate), CALENDAR_MONTH(CreatedDate)
ORDER BY CALENDAR_YEAR(CreatedDate), CALENDAR_MONTH(CreatedDate)

This query groups opportunities by calendar year and month, allowing you to analyze trends in opportunity creation over time.

• Group by Fiscal Periods

SELECT FISCAL_YEAR(CloseDate), FISCAL_QUARTER(CloseDate), SUM(Amount)
FROM Opportunity
WHERE StageName = 'Closed Won'
GROUP BY FISCAL_YEAR(CloseDate), FISCAL_QUARTER(CloseDate)

This query groups closed-won opportunities by fiscal year and quarter, providing insights into revenue performance across different fiscal periods.

---

Conclusion

Mastering date functions in SOQL is essential for any Salesforce developer or administrator. These functions provide powerful tools to filter, manipulate, and analyse date and time data effectively. By understanding and utilising date literals, date functions, time zone conversions, and date calculations, you can create more dynamic and insightful queries and reports.

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