SyntaxShelf

Subtract date

2 snippets in SQL

Also written as subtract dates

SQLSQL

Date Addition / Subtraction

SQL · Date Functions
Syntax
-- PostgreSQL
date + INTERVAL 'n unit'
-- MySQL
DATE_ADD(date, INTERVAL n unit)
-- ANSI
date + INTERVAL 'n' unit
Example
-- PostgreSQL
SELECT
  order_date,
  order_date + INTERVAL '30 days' AS due_date,
  order_date - INTERVAL '1 year' AS year_ago
FROM orders;

-- MySQL
SELECT
  order_date,
  DATE_ADD(order_date, INTERVAL 30 DAY) AS due_date
FROM orders;
Output
-- order_date  | due_date    | year_ago
-- 2025-10-15  | 2025-11-14  | 2024-10-15

Note Interval units: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND. Adding months is tricky - Jan 31 + 1 month may yield Feb 28 or Mar 3 depending on the database. PostgreSQL truncates to end of month; MySQL may overflow.

Date Difference

SQL · Date Functions
Syntax
-- PostgreSQL
date1 - date2  -- returns integer (days)
-- MySQL
DATEDIFF(date1, date2)  -- returns integer (days)
-- Standard: use EXTRACT on interval
Example
-- PostgreSQL
SELECT
  order_date,
  shipped_date,
  shipped_date - order_date AS days_to_ship
FROM orders
WHERE shipped_date IS NOT NULL;

-- MySQL
SELECT
  order_date,
  shipped_date,
  DATEDIFF(shipped_date, order_date) AS days_to_ship
FROM orders;
Output
-- order_date  | shipped_date | days_to_ship
-- 2025-10-01  | 2025-10-04   | 3

Note PostgreSQL subtracts dates directly. MySQL uses DATEDIFF(end, start) - note the argument order is end first. SQL Server's DATEDIFF takes a unit argument: DATEDIFF(DAY, start, end). Always check argument order in your database.

Frequently asked questions

How does SQL handle subtract date?
SQL covers this with 2 copy-ready snippets on this page. The "Date Addition / Subtraction" snippet in SQL uses `-- PostgreSQL`.
Which statement does the SQL example use?
The "Date Addition / Subtraction" snippet uses `-- PostgreSQL`, from the Date Functions section of the SQL cheat sheet.
What other SQL snippets are shown for "subtract date"?
Besides "Date Addition / Subtraction", this page also shows "Date Difference".
Is there anything to watch out for?
Yes. For "Date Addition / Subtraction": Interval units: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND. Adding months is tricky - Jan 31 + 1 month may yield Feb 28 or Mar 3 depending on the database. PostgreSQL truncates to end of month; MySQL may overflow.

Browse all tasks by intent