DATE_TRUNC()
SQL · Date Functions
Theory
DATE_TRUNC rounds a date or timestamp down to the start of a unit. DATE_TRUNC('month', '2026-11-05') gives 2026-11-01, DATE_TRUNC('year', ...) gives 2026-01-01, and DATE_TRUNC('hour', '14:37:12') gives 14:00:00. Unlike EXTRACT, which returns just the number 11, DATE_TRUNC returns a real date that still contains the year, so November 2025 and November 2026 stay separate.
This makes it the standard tool for time-series reports: GROUP BY DATE_TRUNC('month', order_date) gives one row per calendar month. PostgreSQL and Snowflake use DATE_TRUNC('month', d), BigQuery writes DATE_TRUNC(d, MONTH), and SQL Server offers DATETRUNC(month, d) or the older DATEFROMPARTS(YEAR(d), MONTH(d), 1). SQLite has no DATE_TRUNC, but strftime('%Y-%m-01', d) or date(d, 'start of month') produces the same first-of-month value. Week truncation usually starts on Monday, but check your database setting.
Why we use it
Daily data is too detailed for most reports. DATE_TRUNC buckets every date into its week, month or year start, so you can group and chart totals over time with correct, sortable period labels.
When to use it
Use it for monthly or weekly rollups, cohort analysis and time-series charts. Use EXTRACT when you only want a number (month 11) for seasonality across years, and TO_CHAR only to label the final output.
How it works
- Date or timestamp, e.g. 2026-11-05 14:37
- Choose the unit: hour, day, week, month, year
- Reset every smaller part to its start (day 01, 00:00)
- Return a real date: 2026-11-01
- Building a report? Yes: GROUP BY the truncated date. No: Use it as a period label.
- One row per period
Syntax
DATE_TRUNC('unit', date)Example
SELECT DATE_TRUNC('month', hire_date) FROM employees;Key points
- Postgres function, very common for monthly/weekly reporting rollups
- SQLite emulates it by formatting to the start of the unit
- Returns a date, so years stay separate — unlike EXTRACT(MONTH ...)
- BigQuery: DATE_TRUNC(d, MONTH); SQL Server: DATETRUNC(month, d)
- Week start day (Monday vs Sunday) depends on the database