Mixed Date Formats — Solution & ExplanationProblems / Mixed Date Formats / EditorialApproach

Parse defensively. strptime raises an error on the first string that does not match its format — fatal when three formats coexist in one column. try_strptime returns NULL instead, so each format attempt becomes a value you can combine.

Chain with coalesce. coalesce(try_strptime(s, '%Y-%m-%d'), try_strptime(s, '%m/%d/%Y'), try_strptime(s, '%Y/%m/%d')) tries the formats in order and keeps the first success. The formats are mutually exclusive — dashes vs slashes, 4-digit year first vs month first — so order cannot change a value, only resolve it.

Roll up. date_trunc('month', parsed) cast to DATE gives the first-of-month key; COUNT(*) per month and ORDER BY month finish the job.

In the PySpark variant, SQLFrame translates F.to_date(col, fmt) into a strict parse that errors on non-matching rows, so the reliable route is F.expr with the same DuckDB try_strptime + coalesce expression.Key InsightThe try-parse + coalesce chain is the standard idiom for *heterogeneous* string data: convert each candidate interpretation into a nullable value, then let precedence pick the winner. The subtle danger in mixed date columns is silent misparsing, not failed parsing — '05/03/2024' parses happily as both US (May 3) and day-first (March 5) conventions. Here the answer is unambiguous only because the three formats are structurally distinct; the data even includes US strings with day > 12 (like '03/25/2024') that would break a day-first assumption loudly rather than silently.Hints

strptime with the wrong format throws an error and kills the whole query. DuckDB's try_strptime returns NULL on a mismatch instead — which makes it composable.

coalesce picks the first non-NULL result, so chaining try_strptime calls — one per format — implements 'try each format in turn'.

The three formats cannot collide: '%Y-%m-%d' needs dashes, '%m/%d/%Y' needs a 1-2 digit month first, '%Y/%m/%d' needs a 4-digit year first. But the US format is month-first — '05/03/2024' must become May 3rd.

After parsing, it is a routine rollup: date_trunc('month', ...) cast to DATE, then COUNT(*) per month.

Solve Mixed Date Formats yourself →