SQL DATEDIFF: Calculate Difference Between Dates (All Databases)

Overview

Calculating the difference between two dates — in days, months, or years — is different in every database.

MySQL

-- DATEDIFF(date1, date2) → days between:
SELECT DATEDIFF('2026-03-19', '2026-03-12');  -- 7
SELECT DATEDIFF(NOW(), created_at) AS days_ago FROM orders;

-- TIMESTAMPDIFF for other units:
SELECT TIMESTAMPDIFF(MONTH, '2025-01-01', '2026-03-12');  -- 14
SELECT TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) AS age
FROM users;
SELECT TIMESTAMPDIFF(HOUR, created_at, NOW()) AS hours_ago
FROM orders;

PostgreSQL

-- Subtract dates directly (returns interval or integer):
SELECT '2026-03-19'::DATE - '2026-03-12'::DATE;  -- 7 (integer days)

-- AGE function:
SELECT AGE('2026-03-12', '2025-01-01');  -- '1 year 2 mons 11 days'

-- Extract specific parts:
SELECT EXTRACT(YEAR FROM AGE(birth_date)) AS age FROM users;
SELECT EXTRACT(EPOCH FROM (NOW() - created_at)) / 3600 AS hours_ago
FROM orders;

-- DATE_PART:
SELECT DATE_PART('day', NOW() - created_at) AS days_ago FROM orders;

SQL Server

-- DATEDIFF(part, start, end):
SELECT DATEDIFF(DAY, '2026-03-12', '2026-03-19');    -- 7
SELECT DATEDIFF(MONTH, '2025-01-01', '2026-03-12');  -- 14
SELECT DATEDIFF(YEAR, birth_date, GETDATE()) AS age
FROM users;
SELECT DATEDIFF(HOUR, created_at, GETDATE()) AS hours_ago
FROM orders;

-- Parts: YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND

Oracle

-- Subtract dates (returns days as decimal):
SELECT DATE '2026-03-19' - DATE '2026-03-12' FROM DUAL;  -- 7

-- MONTHS_BETWEEN:
SELECT MONTHS_BETWEEN(SYSDATE, DATE '2025-01-01') FROM DUAL;

-- Calculate age in years:
SELECT FLOOR(MONTHS_BETWEEN(SYSDATE, birth_date) / 12) AS age
FROM users;

Skip the Syntax Lookup

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

Frequently Asked Questions