DATEDIFF SQL Compatibility | MySQL, PostgreSQL, SQL Server, Oracle, SQLite

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

SQL DATEDIFF Compatibility Across Databases
Database System	Support Status	Since Version	Notes
MySQL	Native	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	Native	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	Native	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	Native	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	Native	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: Native in all listed versions PostgreSQL: Native in all listed versions SQL Server: Native in all listed versions Oracle: Native in all listed versions SQLite: Native in all listed versions

Per-Database Syntax & Notes

MySQL Native syntax

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 Native syntax

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 Native syntax

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 Native syntax

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 Native syntax

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;