SQL DATE_TRUNC: Truncate Date to Month, Year, Week (All Databases)

Overview

DATE_TRUNC rounds a date down to the start of a given time period (month, week, year). Essential for time-series grouping and reporting.

PostgreSQL

-- DATE_TRUNC(precision, date):
SELECT DATE_TRUNC('month', TIMESTAMP '2026-03-12 14:30:00');
-- '2026-03-01 00:00:00'

SELECT DATE_TRUNC('year', NOW());   -- '2026-01-01 00:00:00'
SELECT DATE_TRUNC('week', NOW());   -- start of this week
SELECT DATE_TRUNC('hour', NOW());   -- current hour start

-- Group by month:
SELECT DATE_TRUNC('month', created_at) AS month,
  SUM(amount) AS revenue
FROM orders
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month;

MySQL

-- No DATE_TRUNC — use DATE_FORMAT or DATE:
SELECT DATE(NOW());  -- '2026-03-12' (truncate to day)

-- Truncate to month:
SELECT DATE_FORMAT(created_at, '%Y-%m-01') AS month
FROM orders;

-- Or LAST_DAY trick:
SELECT DATE_FORMAT(created_at, '%Y-%m-01') AS month,
  SUM(amount) AS revenue
FROM orders
GROUP BY DATE_FORMAT(created_at, '%Y-%m-01');

-- Truncate to year:
SELECT MAKEDATE(YEAR(created_at), 1) AS year_start FROM orders;

SQL Server

-- Cast to date (truncate time):
SELECT CAST(GETDATE() AS DATE);  -- '2026-03-12'

-- Truncate to month:
SELECT DATEFROMPARTS(YEAR(created_at), MONTH(created_at), 1) AS month
FROM orders;

-- Or DATETRUNC (SQL Server 2022+):
SELECT DATETRUNC(MONTH, created_at) AS month FROM orders;

-- Group by month:
SELECT DATEFROMPARTS(YEAR(created_at), MONTH(created_at), 1) AS month,
  SUM(amount) AS revenue
FROM orders
GROUP BY DATEFROMPARTS(YEAR(created_at), MONTH(created_at), 1);

Oracle

-- TRUNC(date, format):
SELECT TRUNC(SYSDATE) FROM DUAL;          -- today at midnight
SELECT TRUNC(SYSDATE, 'MM') FROM DUAL;    -- first of this month
SELECT TRUNC(SYSDATE, 'YYYY') FROM DUAL;  -- first of this year
SELECT TRUNC(SYSDATE, 'IW') FROM DUAL;    -- start of ISO week

-- Group by month:
SELECT TRUNC(created_at, 'MM') AS month,
  SUM(amount) AS revenue
FROM orders
GROUP BY TRUNC(created_at, 'MM');

Skip the Syntax Lookup

Instead of memorizing DATE_TRUNC / TRUNC / DATEFROMPARTS syntax for each database, describe what you need in plain English and let AI2SQL generate the correct query.

Frequently Asked Questions