Truncates a date to the specified granularity.

Sample usage



DATETIME_TRUNC(date_expression, part)


date_expression - a Date or a Date & Time field or expression.

part - the time part to return. DATETIME_TRUNC supports the following 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.
  • 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


Formula Result
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.

ISO Year Boundary:
DATE_TRUNC('2015-06-15', ISOYEAR)
ISO Year Number:
2014-12-29 2015


This function is not available for compatibility mode date types.

Related resources

Was this helpful?
How can we improve it?

Need more help?

Sign in for additional support options to quickly solve your issue