SQL CURRENT_DATE

Returns today's date — no time-of-day component, just the calendar date, straight from the database server.

What & Why

CURRENT_DATE returns the database transaction's calendar date and CURRENT_TIMESTAMP returns its timestamp. They are stable throughout one transaction.

The worked Queryflo result pins the teaching clock to March 20, 2024 so the displayed campaign rows stay reproducible. In a live report, replace those two literals with CURRENT_DATE and CURRENT_TIMESTAMP.

See How It Works

BUSINESS QUESTION

Using a fixed teaching clock, Marketing wants the campaigns active on 2024-03-20 and the report timestamp beside them.

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

-- In a live report, replace these teaching values with CURRENT_DATE
-- and CURRENT_TIMESTAMP.
WITH report_clock AS (
  SELECT
    DATE '2024-03-20' AS current_date,
    TIMESTAMPTZ '2024-03-20 12:00:00+00' AS current_timestamp
)
SELECT
  c.name,
  c.start_date,
  c.end_date,
  clock.current_timestamp AS reported_at
FROM marketing.campaigns c
CROSS JOIN report_clock clock
WHERE c.start_date <= clock.current_date
  AND (c.end_date IS NULL OR c.end_date >= clock.current_date)
ORDER BY c.name;

RESULT — exact output from the displayed Queryflo rows

name	start_date	end_date	reported_at
Finance Retargeting	2024-03-12	2024-05-31	2024-03-20 12:00:00+00
Retention Webinar	2024-02-10	2024-04-15	2024-03-20 12:00:00+00
Spring Launch	2024-01-15	2024-03-31	2024-03-20 12:00:00+00

The fixed teaching clock makes the active-campaign result reproducible.

Intermediate business practice