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 | 45000.00 | 2024-02-10 | 2024-04-15 | active | enterprise | |
| 3 | Finance Retargeting | 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.
Sign up free to try it on a real business scenario