Truncates a date to the specified granularity.
Sample usage
DATETIME_TRUNC(Order Date, MONTH)
Syntax
DATETIME_TRUNC(date_expression, part)
Parameters
date_expression - a Date or a Date & Time field or expression.
part - the time part to return. DATETIME_TRUNC supports the following time parts:
MICROSECOND: available for Date & Time fields or expressions.MILLISECOND: available for Date & Time fields or expressions.SECOND: available for Date & Time fields or expressions.MINUTE: available for Date & Time fields or expressions.HOUR: available for Date & Time fields or expressions.DAYWEEK: This date part begins on Sunday.ISOWEEK: Uses ISO 8601 week boundaries. ISO weeks begin on Monday.MONTHQUARTERYEARISOYEAR: Uses the ISO 8601 week-numbering year boundary. The ISO year boundary is the Monday of the first week whose Thursday belongs to the corresponding Gregorian calendar year.
Return data type
Date & Time
Examples
| Example formula | Output |
|---|---|
DATETIME_TRUNC(DATE '2008-12-25', MONTH) |
2008-12-01 00:00:00 |
|
2008-12-25 00:00:00 |
In the following example, the original date_expression is in the Gregorian calendar year 2015. However, DATE_TRUNC with the ISOYEAR date part truncates the date_expression to the beginning of the ISO year, not the Gregorian calendar year. The first Thursday of the 2015 calendar year was 2015-01-01, so the ISO year 2015 begins on the preceding Monday, 2014-12-29. Therefore the ISO year boundary preceding the date_expression 2015-06-15 is 2014-12-29.
ISO Year Boundary:DATE_TRUNC('2015-06-15', ISOYEAR) |
ISO Year Number: EXTRACT(ISOYEAR FROM DATETIME '2015-06-15') |
| 2014-12-29 | 2015 |
Notes
This function is not available for compatibility mode date types.