SQL Resources
SQL
DATETIME_TRUNC

DATETIME_TRUNC

See also DATE_TRUNC, TIMESTAMP_TRUNC, TIME_TRUNC

Definition

The DATETIME_TRUNC function in BigQuery will truncate the datetime to the given date_part.

Syntax

DATETIME_TRUNC(datetime_expression, date_part)

Where date_part can be any of the following:

  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR
  • 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. 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.
  • 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: DATETIME

    SELECT
      DATETIME_TRUNC(CAST('2021-01-01 02:44:00' AS DATETIME), MILLISECOND) AS month,
      DATETIME_TRUNC(CAST('2021-01-01 02:44:00' AS DATETIME), MINUTE) AS minute,
      DATETIME_TRUNC(CAST('2021-01-01 02:44:00' AS DATETIME), YEAR) AS year
    monthminuteyear
    2021-01-01T02:44:002021-01-01T02:44:002021-01-01T00:00:00

    Practical Info

  • Always returns a DATETIME, so even if you are truncating to the YEAR, you'll receive back the first day of that year at midnight. For that reason, it's often use in conjunction with FORMAT_DATETIME if you want to just use the year YYYY.
  • Common Questions

    How do I aggregate my data by month?

    DATETIME_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,
      DATETIME_TRUNC(date, MONTH) AS month
    FROM
      (
        SELECT
          CAST('2021-02-04 12:50:01' AS DATETIME) AS date,
          3 AS number
        UNION ALL
    (    SELECT
          CAST('2021-02-14 00:42:41' AS DATETIME) AS date,
          7 AS number)
        UNION ALL
    (    SELECT
          CAST('2021-01-04 17:01:00' AS DATETIME) AS date,
          27 AS number)
      ) AS table_3
    GROUP BY
      month
    totalmonth
    102021-02-01T00:00:00
    272021-01-01T00:00:00

    Make sure you've included your DATETIME_TRUNC column in your GROUP BY!

    Troubleshooting Common Errors

    Argument 1 of DATETIME_TRUNC has incorrect type: expected datetime found timestamp.

    You'll need to make sure you're using the right function for your data type. To use DATETIME_TRUNC, you must be working with a DATETIME, and not a DATE, TIMESTAMP, or TIME.

    You can use CAST to change your other date types to DATETIME, or use one of the equivalent functions for other date types like DATE_TRUNC.

    Related Pages

  • TIMESTAMP_TRUNC
  • Dates and Times in Standard SQL
  • Subscribe to newsletter

    Subscribe to receive the latest blog posts to your inbox every week.

    By subscribing you agree to our Privacy Policy.
    Thank you! Your submission has been received!
    Oops! Something went wrong while submitting the form.

    Start solving your organization's biggest problems with Count today.

    Stay up to date with all things count, data and problem solving.