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.

Now You Try

Practice this concept

Using the fixed March 20, 2024 teaching clock, return campaigns active that day and the fixed report timestamp, ordered by name.

Available schema

marketing

Prefix tables with marketing.table_name.

namestart_dateend_datereported_at

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