Fixing Dates When Your CSV Has Three Different Formats

You need to filter orders from Q4 2024. You set a date range filter and get 200 rows. But you know there should be closer to 800. You look at the date column and the problem is obvious:

• 2024-10-15 - ISO format
• 10/15/2024 - US format
• Oct 15 2024 - written format
• 15-Oct-2024 - yet another variation

The column is stored as text, not as a proper date type. The date filter only matched the rows that happened to be in ISO format. The rest were treated as strings and ignored.

This happens when data comes from multiple sources. One system exports ISO dates, another exports US dates, and someone manually entered dates in a written format. When they're all dumped into one CSV, you get a column where sorting goes 01/02/2024, 10/15/2024, 2024-01-15, Apr 3 2024 - alphabetical order, not chronological.

The fix is to parse every format into a real temporal value. Often that's one step. Here's how to do it in ExploreMyData, and how to tell when the one step isn't enough.

Figure out which formats you're dealing with

Before writing any rules, you need to know what you've got. Sort the date column and scroll through it. Look for patterns. In most cases, you'll find 2-3 distinct formats, not 20.

Here are the most common formats in the wild:

• 2024-01-15 - ISO 8601 (YYYY-MM-DD). The good one.
• 01/15/2024 - US format (MM/DD/YYYY). The ambiguous one.
• 15/01/2024 - European format (DD/MM/YYYY). Looks identical to US for days 1-12.
• Jan 15, 2024 or Jan 15 2024 - Written format. At least it's unambiguous.
• 15-Jan-2024 - Another written variant.

The tricky part is telling US and European formats apart. Is 03/04/2024 March 4th or April 3rd? You'll need to know your data source to decide. If you see a value like 13/04/2024, that's definitely European (there's no 13th month). Use clues like that.

Step 1: Try Convert Type with auto-detect on

Start with the easy way. Click the green + in the Pipeline panel and select Convert Type from the Transform group. Pick the date column, set the target type to date, and leave Auto-detect date format ticked, which is how it arrives. Auto-detect tries a long list of patterns per value: ISO, US, European, and named-month forms like Oct 15 2024 and 15-Oct-2024, each with or without a time part. All four formats in the sample above are covered, so one Convert Type step often finishes the job. Apply it, then check the column for NULLs.

Two situations send you on to the manual route. The first is values auto-detect can't read at all. The second is subtler and more dangerous: a column where US and European dates are genuinely mixed. The format list is ordered ISO, then US, then European, and the first pattern that parses wins, so an ambiguous 03/04/2024 comes back as March 4th whether or not that's what the source meant. No NULLs, no warning, just a quietly wrong date.

Step 2: The manual route, one branch per format

Open Update Values from the Transform group, select your date column, and switch the value box from Value to Expression. Nothing is generated for you here. What follows is SQL you type into that box: a CASE WHEN that checks the shape of each value and applies the matching strptime pattern.

CASE WHEN order_date LIKE '____-__-__' THEN strptime(order_date, '%Y-%m-%d') WHEN order_date LIKE '__/__/____' THEN strptime(order_date, '%m/%d/%Y') WHEN order_date SIMILAR TO '[A-Z][a-z]{2} [0-9]+ [0-9]{4}' THEN strptime(order_date, '%b %d %Y') ELSE TRY_CAST(order_date AS DATE) END

Let's break that down:

• ISO format (____-__-__): four characters, dash, two, dash, two. Parsed with %Y-%m-%d.
• US format (__/__/____): two digits, slash, two digits, slash, four digits. Parsed with %m/%d/%Y.
• Written format (starts with letters): matched with a pattern and parsed with %b %d %Y, where %b matches abbreviated month names like "Jan", "Feb".
• Fallback: anything that doesn't match gets a TRY_CAST, which returns NULL instead of throwing an error.

strptime is DuckDB's function for parsing a string into a date using a format pattern. The %Y means four-digit year, %m means two-digit month, %d means two-digit day, and %b means abbreviated month name.

Adding the DD-Mon-YYYY branch

The three branches above don't cover 15-Oct-2024, which is in our sample, so add a fourth WHEN. Detect it by the digit-dash-letters-dash-digits shape and parse it with %d-%b-%Y:

WHEN order_date SIMILAR TO '[0-9]{2}-[A-Z][a-z]{2}-[0-9]{4}' THEN strptime(order_date, '%d-%b-%Y')

Slot that in before the ELSE. Add as many branches as your data needs, though most files have two or three formats and more than four is rare.

What type you actually end up with

A detail worth knowing, because the column header won't tell you: neither route produces a DATE.

The manual route hands the column whatever the expression returns, and strptime returns a TIMESTAMP. So the moment you apply that Update Values step the column stops being text on its own, and the grid badge flips from T to D. There is no follow-up conversion to run.

Convert Type to date lands on TIMESTAMPTZ. With auto-detect on it wraps the column in a try_strptime over its format list, falling back to DuckDB's own cast:

TRY_CAST(TRY_CAST(COALESCE(try_strptime(order_date, [...formats]), TRY_CAST(order_date AS TIMESTAMP)) AS TIMESTAMP) AS TIMESTAMPTZ)

Either way you get a temporal column that sorts chronologically, filters with date operators, and feeds Extract Date Part and Date Difference. Day-level values display as plain dates because there's no time component to show, so in practice it reads as a date column even though the stored type carries a time and a zone.

If you already know the exact pattern, turn auto-detect off and type it into Source date format instead. That forces a single strict parse, which is the other way to settle an ambiguous US versus European column: everything that isn't in your stated format becomes NULL, which is loud, and loud is what you want here.

Check for NULLs after conversion

After converting, sort the date column to push NULLs to the top or bottom. If you see NULLs where you expected dates, those are values that didn't match any of your patterns. Go back to the original data, find those rows, and figure out what format they're in.

Common culprits: dates with commas like Jan 15, 2024 (note the comma), dates with extra spaces, or completely invalid values like N/A or TBD that someone typed into the date field. For the comma variant, add a %b %d, %Y pattern. For the invalid values, those NULLs are actually the correct result - they were never real dates.

Why this matters beyond sorting

Once the column is temporal, everything else unlocks. Open the Column Explorer on it and you get a timeline you can switch between day, week, month, quarter and year. Extract Date Part gives you a year or month column to group on. Date Difference gives you the gap between two date columns. And the filter operators change from string matching to before, after, on or before and on or after.

The root cause is usually that someone exported from Excel, where dates look fine on screen but arrive as text once flattened. Or two teams use different date conventions and nobody noticed until the files were merged. Either way the shape of the fix is the same: see what formats you have, parse them, and check the result for NULLs and for dates that parsed into the wrong month.

Parse your date column →