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.