A Bank CSV With Dates in Two Formats and How to Fix It

A bank statement arrives as a CSV with a Date column, and half the values look like 2026-03-04 while the other half look like 05/03/2026. Someone's tool wrote both, maybe because the file was assembled from two exports, or a person typed some rows into a sheet, or the bank changed formats mid-month. Reading it with one format fails on the other half, and letting a library guess will pick a style per file or per row and not tell you which. The way through is to treat the column as two sub-problems: recognize the styles, then find proof for the order of the ambiguous one. This is the first of the problems in worked CSV problems and the patterns behind them, and the general case of dates is in fixing mixed date formats in a CSV.

The file

Six transactions. Three dates are ISO, three are slashes:

Date,Description,Amount
2026-03-04,CARD PAYMENT ACME,-120.50
05/03/2026,TRANSFER BETA,-89.90
2026-03-06,SALARY,2500.00
13/03/2026,CARD PAYMENT CY,-15.00
2026-03-14,REFUND,45.00
03/04/2026,CARD PAYMENT DELTA,-200.00

Read it as all text first. The profile of the shapes, with each digit replaced by a 9, comes from one query:

SELECT regexp_replace("Date", '[0-9]', '9', 'g') AS shape, count(*) AS n, min("Date") AS example
FROM read_csv('bank_dates.csv', all_varchar=true) GROUP BY 1 ORDER BY n DESC;

shape       n  example
9999-99-99  3  2026-03-04
99/99/9999  3  03/04/2026

The ISO shape is unambiguous: year first, then month, then day. The slash shape is the question. 05/03/2026 is the 5th of March in a day-first style and the 3rd of May in a month-first one. A parser with a locale default will choose one for every slash date, and if it chooses wrongly, the error is silent: both readings are valid dates.

Find proof inside the file

The statement itself holds the answer. A month cannot be above 12, so any slash date whose first part is 13 or more can only be day-first, and any whose second part is above 12 can only be month-first. Parse every slash date both ways and count the failures:

SELECT n, "Date",
       try_strptime("Date", '%d/%m/%Y')::DATE AS as_dmy,
       try_strptime("Date", '%m/%d/%Y')::DATE AS as_mdy
FROM raw WHERE "Date" ~ '^\d{2}/\d{2}/\d{4}$';

n  Date        as_dmy      as_mdy
2  05/03/2026  2026-03-05  2026-05-03
4  13/03/2026  2026-03-13  NULL
6  03/04/2026  2026-04-03  2026-03-04

Row 4 parses only as day-first: month-first gives NULL, since there is no month 13. That one row is the proof, and a summary query says it cleanly: the number of slash dates that fail %d/%m/%Y was 0, and the number that fail %m/%d/%Y was 1. If the styles are consistent within the file, and files are consistent far more often than not, every slash date is day-first. Row 2 is the 5th of March and row 6 is the 3rd of April.

That inference rests on an assumption, that the writer used one convention for all slash dates, and a second, independent check is cheap: the order of the statement.

Convert, then check the order

With the order established, the conversion tries each format in turn. try_strptime returns NULL on a mismatch instead of raising an error, so coalesce picks whichever format works:

SELECT n, coalesce(try_strptime("Date", '%Y-%m-%d'), try_strptime("Date", '%d/%m/%Y'))::DATE AS date,
       "Description" AS description, "Amount"::DECIMAL(10,2) AS amount
FROM raw ORDER BY n;

n  date        description        amount
1  2026-03-04  CARD PAYMENT ACME  -120.50
2  2026-03-05  TRANSFER BETA       -89.90
3  2026-03-06  SALARY             2500.00
4  2026-03-13  CARD PAYMENT CY     -15.00
5  2026-03-14  REFUND               45.00
6  2026-04-03  CARD PAYMENT DELTA -200.00

Read the dates down the column: 4, 5, 6, 13, 14 March, then 3 April. A bank statement is in date order, and these are. That is a second proof, and a strong one for row 6: read as month-first, 03/04/2026 would be 4 March and sit out of order between 14 March and the end of the file. A check in SQL asserts it, comparing each date with the one before:

SELECT count(*) FROM (SELECT date < lag(date) OVER (ORDER BY n) AS back FROM clean) WHERE back;

It returned 0. Put that check into the script as a hard failure, so that a new statement where it fails is stopped and not silently loaded. Also check that no date is NULL after the conversion, which would mean a third style the script does not know, such as 4 Mar 2026 or 04.03.26.

What about the month-first reading of row 2

Row 2 deserves a second thought, since 05/03/2026 is valid both ways and nothing in the row decides. It is decided by the rest of the file. Under the day-first rule it is 5 March, between 4 and 6 March, in order. Under month-first it would be 3 May, and the statement would jump forward two months and back. The ordering check settles it. This is the general technique for ambiguous dates: use any constraint the data offers, such as monotonic order, a known range of dates (a statement for March should not hold dates in May) or another column that carries the date unambiguously, such as a posting timestamp. Constraints that agree are evidence, and a reading that violates one is probably wrong.

Be aware that an ordering test works only when the file is ordered. A file sorted by amount or by description gives no information, and you must not assume order without checking it on the unambiguous rows first: sort the ISO rows by position and confirm they are non-decreasing, and if they are, the assumption holds for the file.

When the file gives no evidence about the date order

Sometimes there is no proving row. A statement for the first week of a month has slash dates like 03/04/2026 and 05/04/2026, all with a first and second part below 13, and the order cannot be inferred from the values. If the file is also not in date order, there is nothing left. The honest options are to ask the source ("is this day-first?"), to take the convention from the system that produced it (a bank in a day-first country writes day-first), or to reject the file with a message that names the ambiguity. Do not choose by the locale of the machine that happens to run the script. That is how a statement is read correctly on one laptop and wrongly in a pipeline on another.

Also decide what to do when a file mixes conventions within the slash style, for instance when two exports were concatenated, one day-first and one month-first. The proof then fails in the other direction: some rows can only be day-first and others only month-first. A file that contains both 13/03/2026 and 03/14/2026 is inconsistent, and the right response is to stop, since no single rule reads it. If you can tell which rows came from which export, for example by a column or by their position, convert each block with its own format.

The cleanest fix is upstream: ask the bank or the tool for ISO 8601 dates, which have one reading everywhere. In the meantime, the profile query, the two-way parse and the order check turn a guess into a derivation. The functions are documented in DuckDB's date format reference, and the surrounding approach in handling time zones in CSV timestamps and reconciling a bank statement CSV with your ledger, which uses the clean statement as input. The ISO format itself is defined in ISO 8601 as summarized by the W3C.