SELECT knowledge FROM sql_resources WHERE category='bigquery-standard-sql' AND slug='date-trunc'

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.

Where date_part can be any of the following:

DATE_TRUNC(date_expression, date_part)

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

month	week	year
2021-01-01	2020-12-27	2021-01-01

Practical Info

Always returns a 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.
Be careful when dealing with multiple TRUNCs - 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
  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

Loading code...

total	month
10	2021-02-01
27	2021-01-01

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.

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