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 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

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:
EXTRACT(ISOYEAR FROM DATETIME '2015-06-15')
2014-12-29 2015

Notes

This function is not available for compatibility mode date types.

Related resources

Was this helpful?
How can we improve it?
Search
Clear search
Close search
Google apps
Main menu
Search Help Center
true
102097
false