Parsing dates that were typed by people

03/04/2026 is two dates

A file from a British or Indian system means 3 April. A file from an American one means 4 March. The file does not say which, and neither does the column name. Parsing it with the wrong pattern does not produce errors for the first twelve days of every month, because 03/04 is valid either way. It produces wrong dates, silently, for about forty percent of the rows, and correct ones for the rest.

Let the data tell you, when it can

If any value in the column has a first part greater than 12, the first part cannot be a month, so the format is day-first:

SELECT count(*) FILTER (WHERE split_part(paid_on, '/', 1)::int > 12) AS first_part_over_12,
       count(*) FILTER (WHERE split_part(paid_on, '/', 2)::int > 12) AS second_part_over_12
FROM raw_payments;

One of those counts should be zero and the other should not. If the first is 3,400 and the second is 0, the file is day-first. If both are zero, which happens on a small file or one covering only the first twelve days of a month, the data cannot tell you, and the only honest step is to ask the person who exported it. A mixed file, where both counts are non-zero, means two formats in one column, usually because two people exported from two systems, and the load_file column from the previous lesson is how you find which rows came from where.

The parsing functions

Postgres: to_date(paid_on, 'DD/MM/YYYY'). The pattern letters are DD day, MM month, YYYY four-digit year, YY two-digit year, HH24:MI:SS for times, and to_timestamp when a time is present. Modern Postgres rejects an impossible date such as 31 February with an error rather than rolling it forward, which is what you want.

DuckDB: strptime(paid_on, '%d/%m/%Y'), with C-style codes. MySQL: STR_TO_DATE(paid_on, '%d/%m/%Y'). SQLite has no parser at all; it expects ISO text and you rearrange the pieces with substr: substr(d, 7, 4) || '-' || substr(d, 4, 2) || '-' || substr(d, 1, 2), which is ugly and works.

Two-digit years

26 is 2026 to a human reading a recent file and 1926 to a birth-date column. Postgres’s YY maps 00 to 69 onto 2000 to 2069 and 70 to 99 onto 1970 to 1999. A customer born in 1968 is parsed as born in 2068. For any column that can hold dates more than fifty years apart, do not accept two-digit years; find the source that has four.

Spreadsheet serial numbers

A date column exported from Excel or Sheets sometimes arrives as 45000. That is not a code; it is the number of days since 30 December 1899, which is how spreadsheets store dates internally. date '1899-12-30' + 45000 is 15 March 2023. The anchor is 30 December rather than 31 because of a leap-year bug in Lotus 1-2-3 that Excel reproduced on purpose in 1987 and has kept ever since. A column that mixes serial numbers and typed text needs a CASE that tests whether the value is all digits and parses each kind its own way.

Times, and the parts people leave out

14:30 and 2:30 PM are the same time in two formats; HH24:MI and HH12:MI AM in Postgres’s pattern language. A value like 2026-03-04 with no time is midnight, and a range filter on it behaves as the grouping-by-time lesson described. A value like 24:00 is rejected by Postgres and accepted by some systems as the end of the day. A value with a trailing Z is UTC; one with +05:30 is an offset; one with neither is a wall-clock reading whose zone lives in somebody’s head, which the earlier lesson on timestamps covered.

The check that stays

After parsing, look at the distribution:

SELECT min(paid_on), max(paid_on), count(*) FILTER (WHERE paid_on IS NULL)
FROM payments_typed;

A minimum of 1 January 1970 means a zero was parsed as a date. A maximum in the future means a day-first file was read month-first, or a two-digit year went the wrong way. A NULL count above zero is the list of values still to be decided. Add a histogram by month and read it: a file covering March with a dozen rows scattered across the other eleven months is a file with a format problem on those dozen rows, and they are usually all from one source.