datesql

Arithmetic

Days Between Two Dates in SQL (DATEDIFF per Dialect)

3 min · updated June 14, 2026

“How many days between start and end?” — where the dialects disagree most on argument order.

Days between (end − start)

-- MySQL  (end first!)
SELECT DATEDIFF(end_d, start_d);

-- PostgreSQL  (date subtraction returns an integer number of days)
SELECT end_d - start_d;

-- BigQuery  (start before end, unit last)
SELECT DATE_DIFF(end_d, start_d, DAY);

-- Snowflake  (unit first, start before end)
SELECT DATEDIFF(day, start_d, end_d);

-- SQLite
SELECT CAST(julianday(end_d) - julianday(start_d) AS INT);

-- DuckDB
SELECT date_diff('day', start_d, end_d);   -- or  end_d - start_d

Months / years between

SELECT TIMESTAMPDIFF(MONTH, start_d, end_d);   -- MySQL  (start, end)
SELECT DATE_DIFF(end_d, start_d, MONTH);       -- BigQuery
SELECT DATEDIFF(month, start_d, end_d);        -- Snowflake / DuckDB-style: date_diff('month',...)
SELECT (EXTRACT(YEAR FROM age(end_d, start_d)) * 12
      + EXTRACT(MONTH FROM age(end_d, start_d)));  -- PostgreSQL (full months)

The two big traps:

  1. Argument order. MySQL DATEDIFF is (end, start); Snowflake/DuckDB/BigQuery put the unit first and start before end. Get it backwards and you get a negative number.
  2. Boundary vs whole units. For DAY everyone counts whole days, but DATEDIFF(month/year, …) in Snowflake/BigQuery counts crossed boundaries, not full months — Jan 31 → Feb 1 is “1 month”. Postgres age() and MySQL TIMESTAMPDIFF count full units instead. Pick the one that matches what you mean (see the age recipe).

← All recipes