DATEDIFF()
SQL · Date Functions
Theory
DATEDIFF tells you how far apart two dates are, as a count of a unit such as days, months or years. If an order was placed on 2026-11-02 and shipped on 2026-11-05, the difference is 3 days. It answers questions like 'how many days did delivery take?', 'how long has each employee worked here?' or 'how overdue is this invoice?'.
Every database spells it differently, and argument order is the classic trap. SQL Server and Snowflake use DATEDIFF(day, start, end) and count unit boundaries crossed, so DATEDIFF(year, '2025-12-31', '2026-01-01') is 1 even though only one day passed. MySQL uses DATEDIFF(end, start) and returns days only. PostgreSQL subtracts dates directly: end_date - start_date gives days. SQLite has no DATEDIFF; you subtract julianday() values and cast the result to an integer. A negative result usually means the arguments are reversed.
Why we use it
Durations drive many business metrics: delivery time, customer age, days overdue, time between visits. DATEDIFF converts two dates into one number you can average, filter or bucket.
When to use it
Use it whenever you need elapsed time between two dates. To produce a new date (start + 30 days) use DATE_ADD instead. For exact ages in years, check how your database counts boundaries, or compare full dates.
How it works
- Two dates: start and end
- Choose the unit: day, month, year
- Unit-first dialect (SQL Server/Snowflake)? Yes: DATEDIFF(day, start, end). No: MySQL: DATEDIFF(end, start).
- Database counts units between them
- Is the result negative? Yes: Arguments are probably reversed. No: Use the number.
- Duration ready to average or filter
Syntax
DATEDIFF(date1, date2)Example
SELECT DATEDIFF(CURRENT_DATE, hire_date) FROM employees;Key points
- SQL Server/MySQL syntax shown; argument order and unit vary by dialect
- SQLite subtracts julianday() values to get a day count
- SQL Server/Snowflake: DATEDIFF(day, start, end); MySQL: DATEDIFF(end, start)
- SQL Server counts boundaries crossed: Dec 31 to Jan 1 is 1 year
- PostgreSQL: end_date - start_date returns whole days for DATE columns