Lesson 5 of 15 · Lab
Parsing dates from text: day first or month first
Parsing a date means telling the software which format the text is in, because 03/08/2026 is March to an American and August to most of Europe. To learn which a file uses, look for a day above 12. 13/08/2026 can only be day first, because there's no month 13.
Some rows fail under the wrong format, while others come out wrong without complaint.
In this lab, 18 invoice rows from fictional Vandelay Coffee Supply are cut down to the two columns you need.
| invoice_id | invoice_date |
|---|---|
| INV-1001 | 30/06/2026 |
| INV-1002 | 02/07/2026 |
| INV-1016 | TBC |
Read month first, what happens to each of these three?
INV-1001 fails, because there's no month 30. INV-1016 fails, because TBC isn't a date. INV-1002 is the dangerous one, since it parses as February 7 and looks fine.
Switch the date format and recount
- This live flow in Strata, Oplema's browser app for data prep, opens with DateTime selected and Text looks like set to US date. In the preview,
invoice_onis NULL for INV-1001 and reads 2026-02-07 for INV-1002. - Select Summarize.
missingcounts the rows that failed: 11 of 18. - Select DateTime and set Text looks like to the EU date option.
- Read the preview again. The first row is now a date, and it isn't in Q3.
- Select Summarize.
missingshould fall to a single row. Scroll DateTime's preview to find it.
Swipe sideways to see the canvas, or open this page on a larger screen to work with it.
What you should find
missing is 1: INV-1016, whose date is TBC. INV-1001 now reads 2026-06-30, a June invoice in a Q3 report. The US format had hidden it behind a NULL.
A date that parsed can still be wrong
Under the US format, seven dates parsed and six of those were wrong, and nothing reported them. A parse only proves that the text fit the pattern you gave it. So check a date column against something you know, such as its range. A Q3 report with February invoices gives itself away.
The block keeps a true date rather than text, which lets later lessons sort and filter by it. The remaining NULL is honest: nobody knows INV-1016's date. A later rule has to decide about it openly. See the DateTime reference.
Common date mistakes
- Testing only low days. 03/04/2026 parses under both formats. Test a day above 12, or ask whoever owns the file.
- Letting a spreadsheet convert on open. Excel guesses from your regional settings, and may turn some rows into dates and leave the rest as text.
- Mixing formats in one column. Merged files often do this, and no single pattern fits them.
Store the result as a real date, and when you need text, use ISO format, 2026-08-03. It's unambiguous, and it sorts correctly even as text.
Parsing dates in SQL and Excel
In SQL, name the format. PostgreSQL and Oracle use TO_DATE(invoice_date, 'DD/MM/YYYY'), DuckDB uses STRPTIME(invoice_date, '%d/%m/%Y')::DATE, and SQL Server uses TRY_CONVERT(date, invoice_date, 103), where 103 means day first.
In Excel, DATEVALUE follows your settings. To state the format, use Text to Columns, choose Date and pick DMY, or set the locale when changing the type in Power Query.