Checking That Every Date in a CSV File Parses Correctly

"Does every date in this column parse?" is a question with a trap in it, because the answer depends on what parse means. A parser is a convenience: it is built to accept as many reasonable spellings as it can, and a validator is built to accept exactly one. The same call can be the right tool for reading a date and the wrong tool for checking one. This article runs 14 awkward values through three parsers, shows where each of them is more forgiving than you want, and ends with a short check that is strict where it should be. It is a companion to fixing mixed date formats, which covers repairing dates, and one of the checks in the validation guide.

The test values

Fourteen values, each chosen because a real file has produced something like it:

id,d
1,2025-03-14
2,2025-02-30
3,2025-13-45
4,2024-02-29
5,2023-02-29
6,03/04/2025
7,2025-3-4
8,0000-00-00
9,1900-01-01
10,2999-12-31
11,
12,2025-03-14 10:30
13,14 Mar 2025
14, 2025-03-15

There are impossible dates (2025-02-30, 2025-13-45, 2023-02-29), a leap day that is real (2024-02-29), a slash date (03/04/2025), an unpadded one (2025-3-4), the zero placeholder some databases export (0000-00-00), two dates so far from the others that they look like defaults (1900-01-01 and 2999-12-31), an empty value, a date with a time, a date with a month name and one with a leading space.

What a strict parse says

The check is strptime with the one format the column is supposed to use, plus pandas to_datetime with the same format and errors="coerce". A third column adds a stricter test: write the parsed date back out and compare it with the original text.

import csv, sys
from datetime import datetime, date

import pandas as pd

rows = list(csv.DictReader(open("/batches/csv-0001-0200/lab/data/val/dates.csv", newline="")))
FMT = "%Y-%m-%d"


def strptime_ok(v):
    try:
        datetime.strptime(v, FMT)
        return True
    except ValueError:
        return False


def strict_ok(v):
    try:
        return datetime.strptime(v, FMT).strftime(FMT) == v
    except ValueError:
        return False


s = pd.Series([r["d"] for r in rows], index=[r["id"] for r in rows], dtype="string")
pdp = pd.to_datetime(s, format=FMT, errors="coerce")
print(f"{'id':>2}  {'value':<18}{'strptime':<10}{'round trip':<12}{'pandas':<12}")
for r in rows:
    v = r["d"]
    p = pdp[r["id"]]
    print(f"{r['id']:>2}  {v!r:<18}{str(strptime_ok(v)):<10}{str(strict_ok(v)):<12}{'NaT' if pd.isna(p) else str(p.date()):<12}")

python lab/val/t0127.py

id  value             strptime  round trip  pandas      
 1  '2025-03-14'      True      True        2025-03-14  
 2  '2025-02-30'      False     False       NaT         
 3  '2025-13-45'      False     False       NaT         
 4  '2024-02-29'      True      True        2024-02-29  
 5  '2023-02-29'      False     False       NaT         
 6  '03/04/2025'      False     False       NaT         
 7  '2025-3-4'        True      False       2025-03-04  
 8  '0000-00-00'      False     False       NaT         
 9  '1900-01-01'      True      True        1900-01-01  
10  '2999-12-31'      True      True        2999-12-31  
11  ''                False     False       NaT         
12  '2025-03-14 10:30'False     False       NaT         
13  '14 Mar 2025'     False     False       NaT         
14  ' 2025-03-15'     False     False       NaT         

Read the table by column.

**strptime** rejected the three impossible dates, 2023-02-29 (2023 is not a leap year), the zero placeholder, the slash date, the month-name date, the empty value, the leading space and the time. It accepted 2025-3-4. %m and %d in strptime match one or two digits, so an unpadded month and day pass a format that looks like it demands two. If your format says %Y-%m-%d and the contract says ISO 8601, 2025-3-4 is not ISO, and strptime has just told you it is fine.

The round trip is the fix. datetime.strptime(v, fmt).strftime(fmt) == v is true only when the text is exactly what the format would produce, so 2025-3-4, which writes back as 2025-03-04, fails. It is two extra calls and removes the whole class of "parsed, but not in the agreed form" values.

pandas with format="%Y-%m-%d" behaved like strptime: it returned NaT for every value that failed and 2025-03-04 for 2025-3-4. The empty string is NaT as well, which is a point to decide deliberately: whether an empty date is missing (allowed) or invalid (an error) is policy, not a parser result. Count the NaT values that came from non-empty text, and separately count the empty ones.

Two further observations. 1900-01-01 and 2999-12-31 parsed without complaint, and they are valid dates; nothing about the parse tells you they are suspect. And 2025-02-30 is the classic: it has the exact shape of a date, so a regular expression passes it and only a real calendar check fails it.

What SQL says

DuckDB has two ways to ask. try_strptime takes a format; try_cast(d AS DATE) takes whatever the engine's date parser accepts. A regex adds the shape test:

duckdb -box -c "
SELECT id, d, try_strptime(d, '%Y-%m-%d') AS strptime_ymd, try_cast(d AS DATE) AS cast_date, regexp_full_match(d, '\\d{4}-\\d{2}-\\d{2}') AS shaped
FROM read_csv('lab/data/val/dates.csv', all_varchar=true, header=true) ORDER BY try_cast(id AS INT)"

┌────┬──────────────────┬─────────────────────┬────────────┬────────┐
│ id │        d         │    strptime_ymd     │ cast_date  │ shaped │
├────┼──────────────────┼─────────────────────┼────────────┼────────┤
│ 1  │ 2025-03-14       │ 2025-03-14 00:00:00 │ 2025-03-14 │ true   │
│ 2  │ 2025-02-30       │ NULL                │ NULL       │ true   │
│ 3  │ 2025-13-45       │ NULL                │ NULL       │ true   │
│ 4  │ 2024-02-29       │ 2024-02-29 00:00:00 │ 2024-02-29 │ true   │
│ 5  │ 2023-02-29       │ NULL                │ NULL       │ true   │
│ 6  │ 03/04/2025       │ NULL                │ NULL       │ false  │
│ 7  │ 2025-3-4         │ 2025-03-04 00:00:00 │ 2025-03-04 │ false  │
│ 8  │ 0000-00-00       │ NULL                │ NULL       │ true   │
│ 9  │ 1900-01-01       │ 1900-01-01 00:00:00 │ 1900-01-01 │ true   │
│ 10 │ 2999-12-31       │ 2999-12-31 00:00:00 │ 2999-12-31 │ true   │
│ 11 │ NULL             │ NULL                │ NULL       │ NULL   │
│ 12 │ 2025-03-14 10:30 │ NULL                │ 2025-03-14 │ false  │
│ 13 │ 14 Mar 2025      │ NULL                │ NULL       │ false  │
│ 14 │  2025-03-15      │ 2025-03-15 00:00:00 │ 2025-03-15 │ false  │
└────┴──────────────────┴─────────────────────┴────────────┴────────┘

try_strptime agreed with Python except for one thing: it accepted the value with a leading space (id 14) and returned a timestamp, not a date. try_cast was more forgiving still. It accepted 2025-3-4, the leading space, and 2025-03-14 10:30, which it turned into the date 2025-03-14, quietly discarding the time. If the column is supposed to hold dates and you cast a date-time to a date, you have changed the data without a message.

None of this makes DuckDB wrong. try_cast is for reading; for checking, put the shape test (regexp_full_match on \d{4}-\d{2}-\d{2}) next to the parse, as in the last column, and treat a row as valid only when both agree. The regex rejected the unpadded date, the leading space and the time, and the parse rejected 2025-02-30, so the two together do what neither does alone. Its limit is the same as the Python one: it says nothing about whether the date is plausible.

Range checks

A valid date can be impossible for the data. A signup date of 1900-01-01 means the system default, and 2999-12-31 is the usual "no end date" placeholder. A range check costs one line and catches the defaults. On the clean customer file I computed the bounds:

python - <<'PY'
import csv, time
from datetime import datetime
import pandas as pd
t=time.perf_counter()
d = pd.read_csv('work/val/customers.csv', dtype=str, keep_default_na=False)['signup_date']
nonblank = d[d != '']
p = pd.to_datetime(nonblank, format='%Y-%m-%d', errors='coerce')
print(len(d), 'values,', len(nonblank), 'not empty,', int(p.isna().sum()), 'fail to parse,', f'{time.perf_counter()-t:.2f}s')
print('earliest', p.min().date(), 'latest', p.max().date())
print('round trip differs:', int((p.dt.strftime('%Y-%m-%d') != nonblank).sum()))
rows = list(csv.reader(open('work/val/customers-bad.csv', newline='')))
head, body = rows[0], rows[1:]
k = head.index('signup_date')
for n, r in enumerate(body, start=2):
    v = r[k] if len(r) > k else ''
    if v:
        try:
            ok = datetime.strptime(v, '%Y-%m-%d').strftime('%Y-%m-%d') == v
        except ValueError:
            ok = False
        if not ok:
            print('line', n, 'signup_date', repr(v))
PY

193708 values, 192806 not empty, 0 fail to parse, 0.35s
earliest 2025-01-01 latest 2025-12-28
round trip differs: 0
line 302 signup_date '2025-13-45'

Of 193,708 values, 192,806 were not empty and all 192,806 parsed against %Y-%m-%d, in 0.35 s. The earliest was 2025-01-01 and the latest 2025-12-28. The round trip differed in no row, so none was in a lenient form. On the faulty copy, the same strict check printed one line: line 302 signup_date '2025-13-45', which is exactly where the fault was planted. The bounds belong in the contract (a signup date is not before the company existed and not after today), and the validator can then fail on anything outside them. For a column like this one, with a known year, even a bound of a single year is a useful alarm.

The slash problem: which way round?

Not every column has a format you can state in advance. When a file contains 03/04/2025, it is March 4 in the US and April 3 nearly everywhere else, and no parser can tell. What you can do is measure. Look at the largest number in each position, because a part above 12 cannot be a month:

python lab/val/t0127b.py lab/data/clean/customers-messy-200k.csv

40038 slash dates; first part max 31, second part max 28
first part > 12 in 240 rows, second part > 12 in 22779 rows
both parts <= 12 (ambiguous on their own) in 17019 rows

The raw messy file has 40,038 slash dates. The first part reached 31 in 240 rows. The second part exceeded 12 in 22,779 rows (so the second part is a day in those: month first). And in 17,019 rows both parts were 12 or below, so those rows are ambiguous on their own. The 240 rows with a first part above 12 look at first like evidence of a second, day-first convention in the same column. They are not. Counting them showed that all 240 are 30/02/2025 or 31/02/2025:

awk -F, '{for(i=1;i<=NF;i++) if ($i ~ /^3[01]\/02\/2025$/) c++} END {print c " values are 30/02 or 31/02 dd/mm dates"}' lab/data/clean/customers-messy-200k.csv

240 values are 30/02 or 31/02 dd/mm dates

No month is 31, and no February has 31 days, so they fit neither order: they are errors, not a second convention. This is a general rule for ambiguity: rely on rows that can only be read one way, and be suspicious when the supposedly different rows cannot be read any way at all. In this file the evidence says month first, and the 17,019 ambiguous rows can be read that way with that evidence. Evidence from the same column is not a contract, however, and the mixed-formats article says what to do when the file really does contain both.

A check I would run

For a column that should be ISO dates:

1. Decide what an empty value means, and count empties separately from failures.

2. Match the shape with a pattern, if the pattern is cheap.

3. Parse with an explicit format, and require the round trip to give the original text back.

4. Check the range against bounds from the contract.

5. Report the count and the first few failing values with their line numbers, not just a pass or fail.

The faulty file in the validation guide uses the first four, and its report names line 302.

How to tell it worked

Run the check over the 14 test values and confirm that ids 1, 4, 9 and 10 pass the parse, and that 9 and 10 are stopped only by the range check. Add your own awkward values: a date from a leap day in a century year (1900-02-29 is not valid, 2000-02-29 is), a date in a different calendar year than expected, and a value with a non-breaking space. Then run it on a real delivery and read the distinct failing values, not only their count, as the pattern-checking article recommends.

When a date parses and is still wrong

Every check here proves a date is a date. None proves it is the right one. 03/04/2025 read as March when the sender meant April is a perfectly valid date. A timestamp written in local time and read as UTC is shifted by hours and sometimes a day. A signup date entered in a form the user could set to anything is a date and not necessarily true. For those, compare the dates with another source, check the distribution by month for a spike on the first or the twelfth, and ask the provider what convention they use. A contract that names the format, the time zone and the permitted range closes most of those gaps.