DATEDIFFFunctions
Buy Me A Coffee

Calculate the difference between two date or timestamp values, returning an integer count of a specified unit (days, months, etc.) or an interval.

Calculates the difference between two dates or timestamps, returning the count of the specified unit (days, months, years). The unit specified varies by database -- MySQL uses DATEDIFF, PostgreSQL uses AGE and EXTRACT, SQL Server uses DATEDIFF.

Compatibility

Show:
Database System Support Status Since Version Notes
MySQL ✓ Supported all DATEDIFF(end, start) returns integer days only — no unit argument. For other units use TIMESTAMPDIFF(unit, start, end). Units: MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR. Note argument order is reversed between DATEDIFF and TIMESTAMPDIFF.
PostgreSQL ✓ Supported all No DATEDIFF function. Use subtraction: date2 - date1 returns integer days for DATE types, INTERVAL for TIMESTAMP types. EXTRACT(epoch FROM ts2 - ts1) converts interval to seconds. AGE(ts2, ts1) returns human-readable interval.
SQL Server ✓ Supported all DATEDIFF(unit, start, end) with explicit unit. Units: year, quarter, month, week, day, hour, minute, second, millisecond, microsecond, nanosecond. Counts boundary crossings, not exact elapsed time — DATEDIFF(year, '2023-12-31', '2024-01-01') returns 1.
Oracle ✓ Supported all No DATEDIFF function. DATE subtraction returns fractional days (a NUMBER). MONTHS_BETWEEN(d1, d2) returns fractional months. For hours/minutes, multiply day difference by 24 or 1440.
SQLite ✓ Supported all No DATEDIFF function. Use julianday() subtraction for day differences. julianday() converts any date/timestamp to a floating-point Julian day number, so subtraction gives fractional days.

Details

SQL Server's DATEDIFF counts boundary crossings, not elapsed time — a well-known gotcha (Dec 31 → Jan 1 = 1 year difference). PostgreSQL's subtraction of DATE columns returns an integer directly, which is the cleanest syntax. Oracle and SQLite both require workarounds (MONTHS_BETWEEN / julianday). MySQL's DATEDIFF is days-only; TIMESTAMPDIFF is needed for other units.

Standard Syntax

-- SQL Server / MySQL: DATEDIFF(unit, start_date, end_date) -- PostgreSQL: end_date - start_date -- returns integer days for dates

Version Support

MySQL: Since all PostgreSQL: Since all SQL Server: Since all Oracle: Since all SQLite: Since all

Per-Database Syntax & Notes

MySQL

DATEDIFF(end, start) returns integer days only — no unit argument. For other units use TIMESTAMPDIFF(unit, start, end). Units: MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR. Note argument order is reversed between DATEDIFF and TIMESTAMPDIFF.

SELECT DATEDIFF('2024-03-15', '2024-01-01'); -- 74 (days) SELECT TIMESTAMPDIFF(MONTH, '2024-01-01', '2024-03-15'); -- 2 SELECT TIMESTAMPDIFF(HOUR, '2024-01-01', NOW()); -- hours since Jan 1

PostgreSQL

No DATEDIFF function. Use subtraction: date2 - date1 returns integer days for DATE types, INTERVAL for TIMESTAMP types. EXTRACT(epoch FROM ts2 - ts1) converts interval to seconds. AGE(ts2, ts1) returns human-readable interval.

SELECT '2024-03-15'::date - '2024-01-01'::date; -- 74 (integer days) SELECT AGE('2024-03-15'::date, '2024-01-01'::date); -- 2 mons 14 days SELECT EXTRACT(epoch FROM NOW() - '2024-01-01'::date); -- seconds SELECT EXTRACT(day FROM '2024-03-15'::date - '2024-01-01'::date);

SQL Server

DATEDIFF(unit, start, end) with explicit unit. Units: year, quarter, month, week, day, hour, minute, second, millisecond, microsecond, nanosecond. Counts boundary crossings, not exact elapsed time — DATEDIFF(year, '2023-12-31', '2024-01-01') returns 1.

SELECT DATEDIFF(day, '2024-01-01', '2024-03-15'); -- 74 SELECT DATEDIFF(month, '2024-01-01', '2024-03-15'); -- 2 SELECT DATEDIFF(year, '2023-12-31', '2024-01-01'); -- 1 (boundary crossing!) SELECT DATEDIFF(hour, '2024-01-01', GETDATE());

Oracle

No DATEDIFF function. DATE subtraction returns fractional days (a NUMBER). MONTHS_BETWEEN(d1, d2) returns fractional months. For hours/minutes, multiply day difference by 24 or 1440.

SELECT SYSDATE - DATE '2024-01-01' FROM DUAL; -- fractional days SELECT MONTHS_BETWEEN(DATE '2024-03-15', DATE '2024-01-01') FROM DUAL; -- 2.45... SELECT TRUNC(SYSDATE - DATE '2024-01-01') FROM DUAL; -- whole days SELECT (SYSDATE - DATE '2024-01-01') * 24 FROM DUAL; -- hours

SQLite

No DATEDIFF function. Use julianday() subtraction for day differences. julianday() converts any date/timestamp to a floating-point Julian day number, so subtraction gives fractional days.

SELECT julianday('2024-03-15') - julianday('2024-01-01'); -- 74.0 SELECT CAST(julianday('now') - julianday('2024-01-01') AS INTEGER); -- whole days -- Hours: SELECT (julianday('now') - julianday('2024-01-01')) * 24;