Date Functions

SQL for CRM Analytics supports DateTime, DateOnly, and Date date types. If you’re familiar with standard SQL, the DateOnly type corresponds with SQL’s Date type and the DateTime type corresponds with SQL’s Timestamp type. DateTime and DateOnly have custom time zone support. If your org doesn’t support custom time zones, then use the Date type, which supports GMT date information only.

A DateTime date type follows this format.

1DateTimeFormat = "2006-01-02 15:04:05"

A DateOnly date type follows this format.

1DateOnlyFormat = "2006-01-02"

Access Parts of a Date

Use the EXTRACT() function to access parts of a date. You can use EXTRACT() in projections, filtering, grouping and ordering.
Project a Date Field

To project a date field of type DateTime, use the EXTRACT() function on the date field in the SELECT statement.
Filter By Date Parts or Date Field

To filter by a date part, use the WHERE clause with the EXTRACT() function, and pass it the desired date parts (year, month, or day). To filter by a date field, use the WHERE clause with a logical operator.
Group By Date Part

To group by date part, use the GROUP BY clause and the EXTRACT() function. Pass EXTRACT() the date parts to isolate.
Order By Date Part

To order by date part, use EXTRACT() on the date field with the ORDER BY clause. The query returns the count of rows containing these same values and orders the results by count descending.
Project a Custom Fiscal Date Part

To project a custom fiscal date part, pass custom fiscal date parts to the EXTRACT() function.
Day in Week, Month, Quarter or Year Functions

Use the following functions to find the position of a day within a week, month, quarter, or year.
First and Last Day in the Week, Month, Quarter or Year Functions

Use the following functions to find the first and last week, month, quarter, or year for both standard and fiscal calendars. These functions accept DateTime, DateOnly, and legacy input values. They return the same type as the input value.