Level 6 · Parse a carrier feed with mixed date formats

A date stored as text is just characters. Before you can subtract, group by month or compare, you have to parse it, and that means telling the database which format the text uses.

strptime and format codes

strptime(text, format) reads text into a TIMESTAMP using format codes:

code	means	example
%Y	4-digit year	2025
%m	month number	11 or 3
%d	day of month	05 or 6
%b	short month name	Nov

SELECT strptime('11/05/2025', '%m/%d/%Y');   -- 2025-11-05 00:00:00

DuckDB’s parser accepts single-digit months and days for %m and %d, so '11/7/2025' also works.

Several formats in one column

strptime raises an error when the text does not match. try_strptime returns NULL instead, which lets you try formats one after another:

CAST(COALESCE(
  try_strptime(delivered_text, '%Y-%m-%d'),
  try_strptime(delivered_text, '%m/%d/%Y'),
  try_strptime(delivered_text, '%b %d, %Y')
) AS DATE) AS delivered_on

DuckDB also takes a list: try_strptime(delivered_text, ['%Y-%m-%d', '%m/%d/%Y']). Whatever is still NULL after the last format is your review list. In the sample, order 11500 was placed on Halloween, 2025-10-31, and delivered on 2025-11-04: 4 days.

Formatting dates back into text

strftime goes the other way, from a date to text in the format you choose. Reference codes often embed a date: CONCAT('ORD-', order_id, '-', strftime(order_date, '%m%d')) gives ORD-11500-1031. CONCAT turns the number into text for you.

Common mistakes

• Ambiguous day and month. '05/11/2025' is May 11 in the US and November 5 in Europe. No query can tell which was meant. Ask the sender for their format, then parse each source with its own rule. '13/11/2025' fails as month/day, a hint that this depot writes the day first.
• Comparing text dates. '11/05/2025' < '2025-11-04' compares characters, not dates.
• Dropping the failures. Filtering out NULL dates hides the deliveries that most need a follow-up.

Dialects and SQL Server

Postgres and Snowflake use TO_DATE(text, 'MM/DD/YYYY') (Snowflake also has TRY_TO_DATE), BigQuery SAFE.PARSE_DATE('%m/%d/%Y', text), MySQL STR_TO_DATE. SQL Server uses style numbers: 101 is mm/dd/yyyy, 103 is dd/mm/yyyy, 23 is yyyy-mm-dd. ISDATE and TRY_PARSE help with checks.

SELECT CONCAT('ORD-', o.order_id, '-', FORMAT(o.order_date, 'MMdd')) AS ship_ref,
       COALESCE(TRY_CONVERT(date, f.delivered_text, 23),
                TRY_CONVERT(date, f.delivered_text, 101)) AS delivered_on
FROM delivery_feed AS f
JOIN orders AS o ON o.order_id = f.order_id;