SQL Days Between Dates

The general form of the Subtract Dates lesson — computing the gap between any two arbitrary date columns, not just against today.

What & Why

Subtracting two DATE columns from each other gives the day count between them — this works identically whether one side is CURRENT_DATE or two entirely different stored columns.

See How It Works

BUSINESS QUESTION

Marketing wants each campaign duration in days.

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

EXAMPLE QUERY

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

RESULT — exact output from the displayed Queryflo rows

campaign_id	name	duration_days
4	Enterprise Search	90
3	Finance Retargeting	80
1	Spring Launch	76
2	Retention Webinar	65

DATE subtraction returns the real completed campaign durations.

Now You Try

Practice this concept

Marketing wants each campaign duration in days.

Available schema

marketing

Prefix tables with marketing.table_name.

campaign_idnameduration_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