Before this: Compare instants, not timestamp text
The idea
03/04/2026 is 4 March in the US and 3 April in the UK. No parser can know which without a declared convention, and guessing silently shifts data by months.
Accept unambiguous ISO dates (YYYY-MM-DD) everywhere. Parse slash dates with the order the row's locale declares. Anything else is ambiguous and goes to exceptions; a date that doesn't exist, like 31/02, is invalid.
Never apply "day first" to a whole column because most rows look European. Mixed sources are exactly where this breaks.
Worked example
Input: ("03/04/2026", en-US) ("03/04/2026", en-GB) ("03/04/2026", missing)
- Visual
Dates by locale → en-US month first, en-GB day first
- SQL (DuckDB)
CASE locale WHEN 'en-US' THEN try_strptime(raw_date, '%m/%d/%Y') WHEN 'en-GB' THEN try_strptime(raw_date, '%d/%m/%Y') END- pandas
month, day = (a, b) if locale == "en-US" else (b, a)
Result: 2026-03-04, 2026-04-03, quarantined as ambiguous_format
Common mistakes
- dayfirst=True for every row.
- Letting the parser guess.
- Converting impossible dates to the next valid day.
Check yourself
Locale fr-FR isn't in the rules. What happens?
Slash dates for it are ambiguous_format.
31/02/2026 with en-GB?
invalid_date: the day doesn't exist.
Practice it
Parse dates by each row's declared locale.
Next: Exact decimals for money