DATETIME_TRUNC
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. -
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 |
DATETIME_TRUNC(DATETIME "2008-12-25 15:30:00", DAY)
|
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.
| Example formula | Output |
|---|---|
DATE_TRUNC('2015-06-15', ISOYEAR)
|
2014-12-29 |
EXTRACT(ISOYEAR FROM DATETIME '2015-06-15')
|
2015 |
Notes
This function is not available for compatibility mode date types.
