DATE TRUNCFunctions
Buy Me A Coffee

Truncate a date or timestamp to a specified unit of precision (year, month, day, hour, etc.), zeroing out all smaller units.

Truncates a date or timestamp to a specified unit, rounding down to the start of that unit. Useful for bucketing events into time windows: date_trunc(hour, created_at). Much faster than EXTRACT for grouping by time periods.

Compatibility

Show:
Database System Support Status Since Version Notes
MySQL ✗ Not Supported — No DATE_TRUNC(). Use DATE_FORMAT() or arithmetic with DATE() and LAST_DAY(). For truncation to a specific unit, combine YEAR/MONTH/DAY extraction and MAKEDATE/MAKETIME.
PostgreSQL ✓ Supported all DATE_TRUNC(unit, timestamp [, timezone]). Units: microseconds, milliseconds, second, minute, hour, day, week, month, quarter, year, decade, century, millennium. Optional timezone argument added in PG 12.
SQL Server ✓ Supported 2022 DATE_TRUNC(datepart, date) added in SQL Server 2022. For older versions, use DATEADD/DATEDIFF arithmetic: DATEADD(month, DATEDIFF(month, 0, col), 0).
Oracle ✓ Supported all TRUNC(date, format). Format strings differ from PostgreSQL: 'MM' for month, 'DD' for day, 'HH' for hour, 'YYYY' for year. No direct equivalent for week truncation.
SQLite ✗ Not Supported — No DATE_TRUNC(). Use strftime() to format and truncate dates.

Details

PostgreSQL's DATE_TRUNC is the gold standard and supports the widest range of units including quarter and decade. Oracle uses TRUNC() with different format strings. MySQL and SQLite have no native truncation — string formatting via DATE_FORMAT/strftime is the workaround. SQL Server only got DATE_TRUNC in 2022; the DATEADD/DATEDIFF pattern is how everyone was doing it before.

Standard Syntax

-- Truncate to start of month: SELECT DATE_TRUNC('month', created_at) AS month_start FROM orders;

Version Support

MySQL: Not supported PostgreSQL: Since all SQL Server: Since 2022 Oracle: Since all SQLite: Not supported

Per-Database Syntax & Notes

MySQL

No DATE_TRUNC(). Use DATE_FORMAT() or arithmetic with DATE() and LAST_DAY(). For truncation to a specific unit, combine YEAR/MONTH/DAY extraction and MAKEDATE/MAKETIME.

-- Truncate to start of month: SELECT DATE_FORMAT(created_at, '%Y-%m-01') AS month_start FROM orders; -- Truncate to start of hour: SELECT DATE_FORMAT(created_at, '%Y-%m-%d %H:00:00') AS hour_start FROM orders;

PostgreSQL

DATE_TRUNC(unit, timestamp [, timezone]). Units: microseconds, milliseconds, second, minute, hour, day, week, month, quarter, year, decade, century, millennium. Optional timezone argument added in PG 12.

SELECT DATE_TRUNC('month', NOW()); -- first of current month SELECT DATE_TRUNC('hour', created_at) FROM orders; -- truncate to hour SELECT DATE_TRUNC('week', NOW()); -- start of ISO week (Monday) SELECT DATE_TRUNC('month', NOW(), 'America/New_York'); -- with timezone (PG 12+)

SQL Server

DATE_TRUNC(datepart, date) added in SQL Server 2022. For older versions, use DATEADD/DATEDIFF arithmetic: DATEADD(month, DATEDIFF(month, 0, col), 0).

-- SQL Server 2022+: SELECT DATE_TRUNC(month, created_at) FROM orders; -- Older versions (workaround): SELECT DATEADD(month, DATEDIFF(month, 0, created_at), 0) FROM orders; SELECT DATEADD(day, DATEDIFF(day, 0, created_at), 0) FROM orders;

Oracle

TRUNC(date, format). Format strings differ from PostgreSQL: 'MM' for month, 'DD' for day, 'HH' for hour, 'YYYY' for year. No direct equivalent for week truncation.

SELECT TRUNC(SYSDATE, 'MM') FROM DUAL; -- first of current month SELECT TRUNC(SYSDATE, 'YYYY') FROM DUAL; -- first of year SELECT TRUNC(SYSDATE, 'DD') FROM DUAL; -- midnight today SELECT TRUNC(SYSDATE, 'HH') FROM DUAL; -- current hour

SQLite

No DATE_TRUNC(). Use strftime() to format and truncate dates.

-- Truncate to start of month: SELECT strftime('%Y-%m-01', created_at) FROM orders; -- Truncate to start of hour: SELECT strftime('%Y-%m-%d %H:00:00', created_at) FROM orders;