SQL TO_DATE

Parses a text string into a real DATE value, using a format pattern you specify explicitly.

What & Why

TO_DATE(text, format) converts a text string into a genuine DATE value. Unlike a simple cast ('2024-06-28'::DATE, which only works for Postgres's default format), TO_DATE takes an explicit format pattern, so it can correctly parse dates written in non-standard layouts.

See How It Works

BUSINESS QUESTION

Marketing validates how campaign start dates would be parsed from an ISO-formatted import field.

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,
  TO_CHAR(start_date, 'YYYY-MM-DD') AS imported_text,
  TO_DATE(TO_CHAR(start_date, 'YYYY-MM-DD'), 'YYYY-MM-DD') AS parsed_start_date
FROM marketing.campaigns
ORDER BY campaign_id;

RESULT — exact output from the displayed Queryflo rows

campaign_id	imported_text	parsed_start_date
1	2024-01-15	2024-01-15
2	2024-02-10	2024-02-10
3	2024-03-12	2024-03-12
4	2024-04-01	2024-04-01

TO_DATE recreates each typed campaign start date from its ISO text.

Now You Try

Practice this concept

Marketing wants to validate ISO campaign start-date text by parsing it back into a typed date.

Available schema

marketing

Prefix tables with marketing.table_name.

campaign_idimported_textparsed_start_date

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