Sample usage
Syntax
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.DAY
WEEK
: This date part begins on Sunday.ISOWEEK
: Uses ISO 8601 week boundaries. ISO weeks begin on Monday.MONTH
QUARTER
YEAR
ISOYEAR
: 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.