SQL to get the days between two dates
Compute the difference in days between two date columns.
SELECT end_date - start_date AS days_between
FROM events;Subtracting two date values yields an integer number of days in PostgreSQL.
SELECT DATEDIFF(end_date, start_date) AS days_between
FROM events;SELECT DATEDIFF(DAY, start_date, end_date) AS days_between
FROM events;SELECT CAST(julianday(end_date) - julianday(start_date) AS INTEGER)
AS days_between
FROM events;How to calculate the number of days between two dates
Most engines have a date-difference function, but the name and argument order vary by dialect.
How it works
- MySQL's DATEDIFF takes (end, start); SQL Server's takes (unit, start, end) — the argument order is different.
- SQLite has no DATEDIFF; julianday() converts each date to a day number you can subtract.
- For timestamps, subtract first then extract days to avoid counting partial days incorrectly.
Related SQL queries
- SQL to get rows from the last 30 days
- SQL to find duplicate rows
- SQL to delete duplicate rows (keep one)
- SQL to find the second highest value
- SQL to find the nth highest value
- SQL to number and rank rows
Frequently asked questions
How do I calculate the number of days between two dates in SQL?
Most engines have a date-difference function, but the name and argument order vary by dialect. MySQL's DATEDIFF takes (end, start); SQL Server's takes (unit, start, end) — the argument order is different. SQLite has no DATEDIFF; julianday() converts each date to a day number you can subtract. For timestamps, subtract first then extract days to avoid counting partial days incorrectly.
Does this work in PostgreSQL, MySQL, SQL Server, and SQLite?
Yes — this page lists the query for each dialect, since the syntax can differ between database engines.
Can I generate this query for my own tables?
Yes. Describe what you want in plain English with Forgly's free AI SQL Generator and it writes the query for your dialect.