Stay organized with collections
Save and categorize content based on your preferences.
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.
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.
[[["Easy to understand","easyToUnderstand","thumb-up"],["Solved my problem","solvedMyProblem","thumb-up"],["Other","otherUp","thumb-up"]],[["Hard to understand","hardToUnderstand","thumb-down"],["Incorrect information or sample code","incorrectInformationOrSampleCode","thumb-down"],["Missing the information/samples I need","missingTheInformationSamplesINeed","thumb-down"],["Other","otherDown","thumb-down"]],["Last updated 2025-07-22 UTC."],[],[],null,["DATETIME_TRUNC\n==============\n\nTruncates a date to the specified granularity.\n\nSample usage\n------------\n\n`DATETIME_TRUNC(Order Date, MONTH)`\n\nSyntax\n------\n\n`DATETIME_TRUNC( `\u003cvar translate=\"no\"\u003edate_expression\u003c/var\u003e`, `\u003cvar translate=\"no\"\u003epart\u003c/var\u003e` )`\n\n### Parameters\n\n\u003cvar translate=\"no\"\u003edate_expression\u003c/var\u003e - a Date or a Date \\& Time field or expression.\n\n\u003cvar translate=\"no\"\u003epart\u003c/var\u003e - the time part to return. `DATETIME_TRUNC` supports the following time parts:\n\n- `MICROSECOND`: available for Date \\& Time fields or expressions.\n- `MILLISECOND`: available for Date \\& Time fields or expressions.\n- `SECOND`: available for Date \\& Time fields or expressions.\n- `MINUTE`: available for Date \\& Time fields or expressions.\n- `HOUR`: available for Date \\& Time fields or expressions.\n- `DAY`\n- `WEEK`: This date part begins on Sunday.\n- `ISOWEEK`: Uses [ISO 8601 week](https://en.wikipedia.org/wiki/ISO_week_date) boundaries. ISO weeks begin on Monday.\n- `MONTH`\n- `QUARTER`\n- `YEAR`\n- `ISOYEAR`: Uses the [ISO 8601](https://en.wikipedia.org/wiki/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.\n\nReturn data type\n----------------\n\nDate \\& Time\n\nExamples\n--------\n\nIn 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.\n\nNotes\n-----\n\nThis function is not available for compatibility mode date types.\n\nRelated resources\n-----------------\n\n- [Dates and times](/looker/docs/studio/dates-and-times)\n- [Calculated fields](/looker/docs/studio/about-calculated-fields)\n- [Looker Studio function list](/looker/docs/studio/function-list)"]]
