Date Functions

Date functions in SOQL queries allow you to group or filter data by date periods such as day, calendar month, or fiscal year.

For example, you could use the CALENDAR_YEAR() function to find the sum of the Amount values for all your opportunities for each calendar year.

1SELECT CALENDAR_YEAR(CreatedDate), SUM(Amount)
2FROM Opportunity
3GROUP BY CALENDAR_YEAR(CreatedDate)

Date functions are available in API version 18.0 and later.

SOQL queries in a client application return dateTime field values as Coordinated Universal Time (UTC) values. To convert dateTime field values to your default time zone, see Converting Time Zones in Date Functions.

Note

This table lists all the date functions supported by SOQL.

Date Function	Description	Examples
`CALENDAR_MONTH()`	Returns a number representing the calendar month of a date field.	`1` for January `12` for December
`CALENDAR_QUARTER()`	Returns a number representing the calendar quarter of a date field.	`1` for January 1 through March 31 `2` for April 1 through June 30 `3` for July 1 through September 30 `4` for October 1 through December 31
`CALENDAR_YEAR()`	Returns a number representing the calendar year of a date field.	`2009`
`DAY_IN_MONTH()`	Returns a number representing the day in the month of a date field.	`20` for February 20
`DAY_IN_WEEK()`	Returns a number representing the day of the week for a date field.	`1` for Sunday `7` for Saturday
`DAY_IN_YEAR()`	Returns a number representing the day in the year for a date field.	`32` for February 1
`DAY_ONLY()`	Returns a date representing the day portion of a dateTime field.	`2009-09-22` for September 22, 2009 You can only use `DAY_ONLY()` with dateTime fields.
`FISCAL_MONTH()`	Returns a number representing the fiscal month of a date field. This differs from `CALENDAR_MONTH()` if your organization uses a fiscal year that does not match the Gregorian calendar. This function is not supported if your organization has custom fiscal years enabled. See "Define Your Fiscal Year" in the Salesforce Help. Note	If your fiscal year starts in March: `1` for March `12` for February See “Set the Fiscal Year” in the Salesforce online help.
`FISCAL_QUARTER()`	Returns a number representing the fiscal quarter of a date field. This differs from `CALENDAR_QUARTER()` if your organization uses a fiscal year that does not match the Gregorian calendar. This function is not supported if your organization has custom fiscal years enabled. See "Define Your Fiscal Year" in the Salesforce Help. Note	If your fiscal year starts in July: `1` for July 15 `4` for June 6
`FISCAL_YEAR()`	Returns a number representing the fiscal year of a date field. This differs from `CALENDAR_YEAR()` if your organization uses a fiscal year that does not match the Gregorian calendar. This function is not supported if your organization has custom fiscal years enabled. See "Define Your Fiscal Year" in the Salesforce Help. Note	`2009`
`HOUR_IN_DAY()`	Returns a number representing the hour in the day for a dateTime field.	`18` for a time of 18:23:10 You can only use `HOUR_IN_DAY()` with dateTime fields.
`WEEK_IN_MONTH()`	Returns a number representing the week in the month for a date field.	`2` for April 10 The first week is from the first through the seventh day of the month.
`WEEK_IN_YEAR()`	Returns a number representing the week in the year for a date field.	`1` for January 3 The first week is from January 1 through January 7.

Note the following when you use date functions:

You can use a date function in a WHERE clause to filter your results even if your query doesn't include a GROUP BY clause. The following query returns data for 2009:
1SELECT CreatedDate, Amount 2FROM Opportunity 3WHERE CALENDAR_YEAR(CreatedDate) = 2009
You can't compare the result of a date function with a date literal in a WHERE clause. The following query doesn't work:
1SELECT CreatedDate, Amount 2FROM Opportunity 3WHERE CALENDAR_YEAR(CreatedDate) = THIS_YEAR
You can't use a date function in a SELECT clause unless you also include it in the GROUP BY clause. There is an exception if the field used in the date function is a date field. You can use the date field instead of the date function in the GROUP BY clause. This doesn't work for dateTime fields. The following query doesn't work because CALENDAR_YEAR(CreatedDate) is not in a GROUP BY clause:
1SELECT CALENDAR_YEAR(CreatedDate), Amount 2FROM Opportunity
The following query works because the date field, CloseDate, is in the GROUP BY clause. This wouldn't work for a dateTime field, such as CreatedDate.
1SELECT CALENDAR_YEAR(CloseDate) 2FROM Opportunity 3GROUP BY CALENDAR_YEAR(CloseDate)

Force.com SOQL and SOSL Reference

Date Functions