Reading a CSV With Mixed Date Formats in DuckDB Safely

A column of dates written five different ways is what a CSV looks like after several people have typed into the same spreadsheet. Reading it with a tool that guesses gives you one of two bad results: a text column that no date function accepts, or a date column that looks right and has silently swapped days and months. This article takes a six row file with five formats, shows what DuckDB does with it, parses every format with a short expression, finds the rows that do not parse, and then covers the one trap that no amount of parsing fixes. The sniffer's date handling is part of the type detection described earlier in this section and in the auto detection reference, and the same task in pandas is in parsing dates while reading a CSV.

The file

Six rows, one date column, five ways of writing a date and one value that is not a date:

id,joined
1,2025-03-14
2,03/14/2025
3,14 Mar 2025
4,"March 14, 2025"
5,2025-3-5
6,not a date

ISO with zero padding, American month first with slashes, day, abbreviated month and year, the long form with a comma (quoted in the file because of the comma), ISO without zero padding, and a placeholder.

What the sniffer does

A date column needs a single format, and this one has none. The sniffer tries its date formats and finds that no one of them parses all six values, so it gives up on dates and falls back to text:

duckdb -box -c "DESCRIBE SELECT * FROM read_csv('mixed-dates.csv')" -c "SELECT * FROM read_csv('mixed-dates.csv')"

┌─────────────┬─────────────┬──────┬──────┬─────────┬───────┐
│ column_name │ column_type │ null │ key  │ default │ extra │
├─────────────┼─────────────┼──────┼──────┼─────────┼───────┤
│ id          │ BIGINT      │ YES  │ NULL │ NULL    │ NULL  │
│ joined      │ VARCHAR     │ YES  │ NULL │ NULL    │ NULL  │
└─────────────┴─────────────┴──────┴──────┴─────────┴───────┘
┌────┬────────────────┐
│ id │     joined     │
├────┼────────────────┤
│ 1  │ 2025-03-14     │
│ 2  │ 03/14/2025     │
│ 3  │ 14 Mar 2025    │
│ 4  │ March 14, 2025 │
│ 5  │ 2025-3-5       │
│ 6  │ not a date     │
└────┴────────────────┘

joined is VARCHAR and every value is shown exactly as written, from 2025-03-14 to not a date. This is the behavior I want. The sniffer did not guess, did not drop a row and did not invent a date for the placeholder; it handed you the text and left the decision to you. Everything below is the decision.

Parse several formats with coalesce

strptime parses a string with a format and raises an error when it does not match. try_strptime returns NULL instead. Put one call per format inside coalesce, which returns the first non-NULL argument, and every value is tried against each format in turn until one fits:

duckdb -box -c "
SELECT id, joined,
       coalesce(try_strptime(joined, '%Y-%m-%d'),
                try_strptime(joined, '%m/%d/%Y'),
                try_strptime(joined, '%d %b %Y'),
                try_strptime(joined, '%B %d, %Y'),
                try_strptime(joined, '%Y-%m-%-d')) ::DATE AS parsed
FROM read_csv('mixed-dates.csv', all_varchar=true) ORDER BY id"

┌────┬────────────────┬────────────┐
│ id │     joined     │   parsed   │
├────┼────────────────┼────────────┤
│ 1  │ 2025-03-14     │ 2025-03-14 │
│ 2  │ 03/14/2025     │ 2025-03-14 │
│ 3  │ 14 Mar 2025    │ 2025-03-14 │
│ 4  │ March 14, 2025 │ 2025-03-14 │
│ 5  │ 2025-3-5       │ 2025-03-05 │
│ 6  │ not a date     │ NULL       │
└────┴────────────────┴────────────┘

Rows 1 to 5 all became 2025-03-14 or, for the last, 2025-03-05: the ISO value, the slash date, the abbreviated month, the long month and the unpadded ISO date. Row 6, not a date, is NULL. The format codes are %Y for a four digit year, %m for a month number, %d for the day, %b for an abbreviated month name, %B for the full name, and %-d for a day without zero padding. I needed %Y-%m-%-d for the fifth value, since %d expects two digits. I read the file with all_varchar=true so that the column arrives as text for try_strptime to work on, and cast the result to DATE at the end because try_strptime returns a timestamp.

The order of the formats matters when two of them could match the same text. The unambiguous formats go first, and the formats that could be read two ways go last or are decided by you in a separate step, which the next section is about.

Find the rows that fail

Every NULL in the parsed column is a value no format matched. Whether that is acceptable depends on the data, and the way to decide is to list them:

duckdb -box -c "
WITH p AS (SELECT id, joined, coalesce(try_strptime(joined, '%Y-%m-%d'), try_strptime(joined, '%m/%d/%Y'), try_strptime(joined, '%d %b %Y'), try_strptime(joined, '%B %d, %Y'), try_strptime(joined, '%Y-%m-%-d')) AS parsed FROM read_csv('mixed-dates.csv', all_varchar=true))
SELECT id, joined FROM p WHERE parsed IS NULL"

┌────┬────────────┐
│ id │   joined   │
├────┼────────────┤
│ 6  │ not a date │
└────┴────────────┘

One row: 6, not a date. On six rows that is trivial, but on a file of a million rows the same query is the difference between knowing that 212 values are bad and finding out when a report is short. I run it every time, and I treat a non-empty result as a reason to look at the source before adding another format to the expression. A format added to make a bad value parse is how a typo becomes a date.

The shortcut that loses rows

DuckDB also lets you declare a format on the reader, with dateformat. It is the right tool for a column with one format and the wrong tool for this one:

duckdb -box -c "SELECT * FROM read_csv('mixed-dates.csv', dateformat='%m/%d/%Y', types={'joined': 'DATE'}, ignore_errors=true)"

┌────┬────────────┐
│ id │   joined   │
├────┼────────────┤
│ 2  │ 2025-03-14 │
└────┴────────────┘

With dateformat='%m/%d/%Y', a declared DATE type and ignore_errors=true, the result has one row, id 2, the only value in that format. The other five rows were dropped with no message. ignore_errors does what it says, and with mixed formats nearly everything is an error, so nearly everything is ignored. The lesson is not that the option is bad. It is that a result that is smaller than the file should be treated as a problem until you have counted it, and the bad rows article shows how to count what was skipped.

The trap parsing cannot fix

The worst mixed-format column is the one that is consistent in form and ambiguous in meaning. Two dates written as 03/04/2025 and 04/05/2025 are valid in both the month first and the day first convention, and a reader can only guess. I made that two row file and read it twice:

printf 'id,d\n1,03/04/2025\n2,04/05/2025\n' > ambiguous.csv
duckdb -box -c "SELECT id, d FROM read_csv('ambiguous.csv')" -c "SELECT id, d FROM read_csv('ambiguous.csv', dateformat='%m/%d/%Y')"

┌────┬────────────┐
│ id │     d      │
├────┼────────────┤
│ 1  │ 2025-04-03 │
│ 2  │ 2025-05-04 │
└────┴────────────┘
┌────┬────────────┐
│ id │     d      │
├────┼────────────┤
│ 1  │ 2025-03-04 │
│ 2  │ 2025-04-05 │
└────┴────────────┘

The first table is the default read. 03/04/2025 became 2025-04-03 and 04/05/2025 became 2025-05-04, which is the day first reading, 3 April and 4 May. The second table, with dateformat='%m/%d/%Y', gives 2025-03-04 and 2025-04-05, 4 March and 5 April. Both are legal. Both are silent. A US export read with the defaults has its days and months swapped in every row where the day is 12 or lower, and the rows where the day is 13 or higher either fail or sort into a different place, so you may notice some of the damage and miss the rest.

The only real fix is to know. Ask the person who made the file, or look for a row that settles it: a value like 13/04/2025 cannot be month first, and a value like 04/13/2025 cannot be day first. If a file contains both kinds of row, it came from more than one source and has to be split before parsing. Then state the format explicitly on the reader, as above, and write it in the script so nobody has to infer it again.

How to tell it worked

Compare the count of rows read with the count of lines in the file, minus the header. Count the NULLs in the parsed column and read each one. Check the minimum and maximum of the parsed dates with SUMMARIZE: a date in 1970 or 2099 is a parse that went wrong. And spot check a few rows against the source, choosing ones where the day is 12 or lower, since those are the ones that can be swapped.

What this cannot decide for you

No expression can tell whether 03/04/2025 is March or April, and none can tell what not a date should have been. Time zones are a separate problem: a bare timestamp such as 2025-03-14 10:15:00 carries no zone, so what it means depends on the system that wrote it. Two digit years are another, and I did not test how %y resolves them, so check the result around the century boundary before trusting it. When the dates drive money or compliance, parse strictly, list the failures and have a person decide what each one means.