Date Functions

Date functions allow you to group or filter your data by various date periods. 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.

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

Date functions are available in API version 18.0 and later.

Note

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.

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. Note This function is not supported if your organization has custom fiscal years enabled. See "About Fiscal Years" in the Salesforce online help.	If your fiscal year starts in March: `1` for March `12` for February See “Setting 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. Note This function is not supported if your organization has custom fiscal years enabled. See "About Fiscal Years" in the Salesforce online help.	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. Note This function is not supported if your organization has custom fiscal years enabled. See "About Fiscal Years" in the Salesforce online help.	`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:
```
SELECT CreatedDate, Amount
FROM Opportunity
WHERE 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:
```
SELECT CreatedDate, Amount
FROM Opportunity
WHERE 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:
```
SELECT CALENDAR_YEAR(CreatedDate), Amount
FROM 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.
```
SELECT CALENDAR_YEAR(CloseDate)
FROM Opportunity
GROUP BY CALENDAR_YEAR(CloseDate)
```