Forgly

SQL to get the days between two dates

Calculate the number of days between two dates in SQL — DATEDIFF in MySQL and SQL Server, date subtraction in PostgreSQL, and julianday() in SQLite.

PostgreSQL
SELECT end_date - start_date AS days_between
FROM events;

Subtracting two date values yields an integer number of days in PostgreSQL.

MySQL
SELECT DATEDIFF(end_date, start_date) AS days_between
FROM events;
SQL Server
SELECT DATEDIFF(DAY, start_date, end_date) AS days_between
FROM events;
SQLite
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

To get the number of days between two dates in SQL, subtract them or call your dialect's date-diff function. Copy-paste examples below for MySQL and SQL Server (DATEDIFF), PostgreSQL (date subtraction), and SQLite (julianday) — mind that the argument order differs by engine.

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

Frequently asked questions

How do I calculate the number of days between two dates in SQL?

To get the number of days between two dates in SQL, subtract them or call your dialect's date-diff function. Copy-paste examples below for MySQL and SQL Server (DATEDIFF), PostgreSQL (date subtraction), and SQLite (julianday) — mind that the argument order differs by engine. 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.