How to TRUNCATE DATE in BigQuery

How do I truncate a date to month, quarter, or year in BigQuery?

DATE_TRUNC in BigQuery rounds a DATE to the first day of the specified calendar part (YEAR, QUARTER, MONTH, WEEK, or DAY).

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Why would I truncate a date in BigQuery?

A retailer might need monthly revenue totals. Truncating order dates to the first day of each month simplifies GROUP BY logic and improves query clarity.

How does DATE_TRUNC work?

DATE_TRUNC(date_expression, part) converts every supplied DATE to the first day of the requested part. The result always retains DATE type, making it safe for joins and aggregations.

Which date parts are supported?

YEAR, QUARTER, MONTH, WEEK, and DAY are valid.BigQuery rejects unsupported parts with an error, so always validate the part before running production SQL.

What is the basic syntax?

Use DATE_TRUNC(date_column, part). Place part in uppercase and enclose in quotes if you prefer string literals. Aliasing the result keeps downstream SQL readable.

How do I aggregate monthly revenue?

First truncate Orders.order_date to MONTH, then GROUP BY the truncated value while summing total_amount. The example below demonstrates.

Can I truncate to custom fiscal years?

BigQuery DATE_TRUNC is calendar-based.For non-January fiscal years, add interval offsets before truncation, then reverse the offset after aggregation.

Best practice for indexes and partitions?

If your table is partitioned by DATE, apply DATE_TRUNC only after the partition filter. Filtering on raw partitions first prevents full-table scans.

Example: Monthly customer order counts

The query in the next section shows how to count orders per customer per month using DATE_TRUNC and standard ecommerce tables.

Why How to TRUNCATE DATE in BigQuery is important

How to TRUNCATE DATE in BigQuery Example Usage


-- Monthly revenue by product category
WITH monthly_sales AS (
  SELECT 
    DATE_TRUNC(o.order_date, MONTH) AS month_start,
    oi.product_id,
    SUM(oi.quantity * p.price)      AS revenue
  FROM Orders o
  JOIN OrderItems oi ON o.id = oi.order_id
  JOIN Products p   ON p.id = oi.product_id
  GROUP BY month_start, oi.product_id
)
SELECT month_start, product_id, revenue
FROM monthly_sales
ORDER BY month_start, revenue DESC;

How to TRUNCATE DATE in BigQuery Syntax


DATE_TRUNC(date_expression, part)

-- date_expression: A column or literal of type DATE.
-- part: DATE_PART keyword. Allowed: YEAR | QUARTER | MONTH | WEEK | DAY.

-- Example using ecommerce schema
SELECT 
  DATE_TRUNC(order_date, MONTH)   AS order_month,
  customer_id,
  SUM(total_amount)               AS monthly_sales
FROM Orders
GROUP BY order_month, customer_id
ORDER BY order_month, customer_id;

Common Mistakes

Using DATE_TRUNC on TIMESTAMP columns directly. DATE_TRUNC only accepts DATE. Use TIMESTAMP_TRUNC or CAST the TIMESTAMP to DATE first.
Applying DATE_TRUNC before partition filtering. This forces BigQuery to scan entire partitions. Filter on the raw date, then truncate for aggregation.