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;

Intermediate business practice