--- schema_version: "1" id: b58004a26c6d113208caf67b3d8b24c7382411d5cc797e60d498b2c6b5a4ea5f title: Date Functions collection: developer version: current locale: en-us portal_url: https://docs.salesforce.com/developer/current/en-us/platform/salesforce-soql-sosl/guides/soql-sosl/sforce-api-calls-soql-select-date-functions.md canonical_url: https://developer.salesforce.com/docs/platform/salesforce-soql-sosl/guide/sforce-api-calls-soql-select-date-functions.html source_site: developer.salesforce.com content_type: documentation content_hash: 335c8faddac7de6b1ff4d0fe4a7b5971a1a9f48c8978b071b73dd3583f80764b products: - SOQL and SOSL Reference - Platform Developer Content --- # 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. ```soql SELECT CALENDAR_YEAR(CreatedDate), SUM(Amount) FROM Opportunity GROUP BY CALENDAR_YEAR(CreatedDate) ``` **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 [convertTimezone()](https://developer.salesforce.com/docs/platform/salesforce-soql-sosl/guide/sforce-api-calls-soql-select-convert-time-zone.md). 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, or `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 date portion of a dateTime field. You can only use `DAY_ONLY()` with dateTime fields. | `2009-09-22` for September 22, 2009 | | `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. | If your fiscal year starts in March: `1` for March, `12` for February | | `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. | 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. | `2009` | | `HOUR_IN_DAY()` | Returns a number representing the hour in the day for a dateTime field. You can only use HOUR_IN_DAY() with dateTime fields. | `18` for a time of 18:23:10 | | `WEEK_IN_MONTH()` | Returns a number representing the week in the month for a date field. The first week is from the first through the seventh day of the month. | `2` for April 10 | | `WEEK_IN_YEAR()` | Returns a number representing the week in the year for a date field. The first week is from January 1 through January 7. | `1` for January 3 | 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: ```soql SELECT CreatedDate, Amount FROM Opportunity WHERE CALENDAR_YEAR(CreatedDate) = 2009 ``` - You can't compare the result of a date function with a [date literal](https://developer.salesforce.com/docs/platform/salesforce-soql-sosl/guide/sforce-api-calls-soql-select-dateformats.md) in a `WHERE` clause. The following query doesn't work: ```soql 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: ```soql 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`. ```soql SELECT CALENDAR_YEAR(CloseDate) FROM Opportunity GROUP BY CALENDAR_YEAR(CloseDate) ```