Truncate a DATE to the start of a specified calendar unit.
TRUNC(date, unit)## Overview Truncates a DATE to the start of the specified calendar unit. This is the DATE-specific counterpart to DATE_TRUNC: the input must be a DATE and the result is a DATE. Use TRUNC when you want to snap a date to the start of its containing month, quarter, or year without introducing a time-of-day component. ## Behavior - Returns a DATE. - Returns NULL if either argument is NULL. - Supported units: year, quarter, month, week, day. - 'day' returns the input date unchanged. - 'week' returns the Monday of the containing ISO week. - 'quarter' returns the first day of the containing calendar quarter (January 1, April 1, July 1, October 1). - Accepts DATE input; TIMESTAMP is implicitly cast to DATE before truncation. ## Compatibility - Date-level truncation aligned with the DATE_TRUNC convention. Use DATE_TRUNC for timestamp-level truncation at sub-day units.
| Name | Type | Description |
|---|---|---|
date | Specifies the date value to truncate. | |
unit | Specifies the truncation precision. Accepts 'year', 'quarter', 'month', 'week', 'day'. Case-insensitive. |
-- First day of the month
SELECT TRUNC(DATE '2025-03-15', 'month') AS month_start;-- First day of the year
SELECT TRUNC(DATE '2025-07-04', 'year') AS year_start;-- Start of the quarter
SELECT TRUNC(DATE '2025-08-20', 'quarter') AS quarter_start;-- Monthly sales rollup
SELECT TRUNC(sale_date, 'month') AS month_start, SUM(amount) AS total
FROM commerce.sales.daily_totals
GROUP BY month_start
ORDER BY month_start;-- Week start (ISO Monday)
SELECT TRUNC(DATE '2025-03-15', 'week') AS week_start;-- Combine with DATEDIFF to compute days-into-month
SELECT order_id, DATEDIFF(order_date, TRUNC(order_date, 'month')) AS day_of_month_offset
FROM commerce.sales.orders;