How does the SQL version of DATEDIF work?

Subtracting two dates in PostgreSQL gives the number of days, like the "d" unit. AGE returns a calendar-aware gap in years, months and days; the year and month parts give complete months and years. Rows with no delivery date are excluded, since the gap is unknown.

Why can SQL give a different result from DATEDIF?

DATEDIF is an undocumented Excel function with known edge-case quirks; AGE follows calendar rules consistently.

DATEDIF in SQL

DATEDIF returns the gap between two dates in days, months or years; in SQL you subtract dates for days and use AGE for complete months and years.

The Excel version

=DATEDIF(A2, B2, "d"), =DATEDIF(A2, B2, "m"), =DATEDIF(A2, B2, "y")

The SQL equivalent

This example assumes: orders(id bigint, ordered_on date, delivered_on date). It is written in PostgreSQL syntax, and your table and column names will differ from these.

SELECT id,
       delivered_on - ordered_on AS days,
       EXTRACT(YEAR FROM AGE(delivered_on, ordered_on)) * 12
         + EXTRACT(MONTH FROM AGE(delivered_on, ordered_on)) AS complete_months,
       EXTRACT(YEAR FROM AGE(delivered_on, ordered_on)) AS complete_years
FROM orders
WHERE delivered_on IS NOT NULL
ORDER BY id;

How it works

Subtracting two dates in PostgreSQL gives the number of days, like the "d" unit.
AGE returns a calendar-aware gap in years, months and days; the year and month parts give complete months and years.
Rows with no delivery date are excluded, since the gap is unknown.

Where the results differ from Excel

DATEDIF is an undocumented Excel function with known edge-case quirks; AGE follows calendar rules consistently.
DATEDIF returns an error if the start date is after the end date; SQL returns a negative number.
Other databases use their own functions: DATEDIFF in MySQL and SQL Server, DATE_DIFF in BigQuery.

Skip the query: ask in plain English

If you would rather not write SQL, type the question. SchemaChat turns it into SQL against your connected database or uploaded file, runs it, and shows the SQL and the figures it used so you can check the answer before you share it. Check that the result matches how your spreadsheet defines the number.

How many days did each order take to be delivered?
What is the average delivery time in days by month?