SQL Subtract Dates

Subtracting one DATE from another gives you a plain number — the count of days between them, not a date.

What & Why

date1 - date2 returns an integer: the number of days separating the two dates. This is genuinely different from subtracting a number FROM a date (which gives another date) — subtracting two dates FROM each other gives a plain count.

See How It Works

BUSINESS QUESTION

Marketing wants the duration of completed campaigns.

id	name	channel	spend	start_date	end_date	status	target_segment
1	Spring Launch	google_ads	55000.00	2024-01-15	2024-03-31	active	smb
2	Retention Webinar	email	45000.00	2024-02-10	2024-04-15	active	enterprise
3	Finance Retargeting	linkedin	50000.00	2024-03-12	2024-05-31	active	enterprise
4	Enterprise Search	google_ads	60000.00	2024-04-01	2024-06-30	active	enterprise

Watch DATE subtraction produce elapsed daysExample 1 of 4

end_date - start_date

id	name	start_date	end_date	duration_days
1	Spring Launch	2024-01-15	2024-03-31	76
2	Retention Webinar	2024-02-10	2024-04-15	?
3	Finance Retargeting	2024-03-12	2024-05-31	?
4	Enterprise Search	2024-04-01	2024-06-30	?

The later date minus the earlier date produces Spring Launch's duration.

EXAMPLE QUERY

SELECT
  id,
  name,
  start_date,
  end_date,
  end_date - start_date AS duration_days
FROM marketing.campaigns
WHERE end_date IS NOT NULL
ORDER BY id;

Now You Try

Practice this concept

Marketing wants the elapsed day count for every completed campaign in the same order as the displayed source table.

Available schema

marketing

Prefix tables with marketing.table_name.

idnamestart_dateend_dateduration_days

marketing.campaigns

Column	Type
id	integer
name	text
channel	text
spend	numeric
start_date	date
end_date	date
status	text
target_segment	text
legacy_id	text

query.sql

Intermediate business practice