Looker Studio is now called Data Studio. Learn more about this change.

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.