DATE_TRUNC
See also DATETIME_TRUNC, TIMESTAMP_TRUNC, TIME_TRUNC
Definition
The DATE_TRUNC
function in BigQuery will truncate the date to the given date_part
.
Syntax
DATE_TRUNC(date_expression, date_part)
Where date_part
can be any of the following:
DAY
WEEK
WEEK()
: Truncates date_expression
to the preceding week boundary, where weeks begin on WEEKDAY
. Valid values for WEEKDAY
are SUNDAY
, MONDAY
, TUESDAY
, WEDNESDAY
, THURSDAY
, FRIDAY
, and SATURDAY
.ISOWEEK
: Truncates date_expression
to the preceding ISO 8601 week boundary. ISOWEEK
s begin on Monday. The first ISOWEEK
of each ISO year contains the first Thursday of the corresponding Gregorian calendar year. Any date_expression
earlier than this will truncate to the preceding Monday.MONTH
QUARTER
YEAR
ISOYEAR
: Truncates date_expression
to the preceding 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.Returns: DATE
SELECT
DATE_TRUNC(CAST('2021-01-01' AS DATE), MONTH) AS month,
DATE_TRUNC(CAST('2021-01-01' AS DATE), WEEK) AS week,
DATE_TRUNC(CAST('2021-01-01' AS DATE), YEAR) AS year
Practical Info
DATE
, so even if you are truncating to the YEAR
, you'll received back the first day of that year. For that reason, it's often use in conjunction with FORMAT_DATE
if you want to just use the year YYYY
. TRUNC
s - as seen in the example above, a date in 2021 can be counted in 2020 if the first day of the week was there. So if you counted by YEAR
vs YEAR(WEEK)
you would get different results. Common Questions
How do I aggregate my data by month?
DATE_TRUNC
is very handy for aggregating your data by a particular date_part
, like MONTH
. See the example below to see how you can aggregate by MONTH
:
SELECT
SUM(number) AS total,
DATE_TRUNC(date, MONTH) AS month
FROM
(
SELECT
CAST('2021-02-04' AS DATE) AS date,
3 AS number
UNION ALL
( SELECT
CAST('2021-02-14' AS DATE) AS date,
7 AS number)
UNION ALL
( SELECT
CAST('2021-01-04' AS DATE) AS date,
27 AS number)
) AS table_3
GROUP BY
month
Make sure you've included your DATE_TRUNC
column in your GROUP BY
!
Troubleshooting Common Errors
Argument 1 of DATE_TRUNC has incorrect type: expected date found timestamp.
You'll need to make sure you're using the right function for your data type. To use DATE_TRUNC
, you must be working with a DATE
, and not a DATETIME
, TIMESTAMP
, or TIME
.
You can use CAST
to change your other date types to DATE
, or use one of the equivalent functions for other date types like DATETIME_TRUNC
.