---
title: "How to parse dates from text | Oplema"
description: "Learn to parse dates from text by stating the format, why 03/08/2026 is ambiguous, how a day above 12 tells you and how to do it in Excel and SQL."
url: "https://oplema.com/learn/data-preparation/parse-text-and-dates"
updated: "2026-11-04"
---

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](https://oplema.com/learn/data-preparation/what-is-data-prep) are cut down to the two columns you need.

Three rows from the stacked exports, dates still text
| 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

1. This live flow in [Strata](https://oplema.com/strata), Oplema's browser app for data prep, opens with **DateTime** selected and **Text looks like** set to **US date**. In the preview, `invoice_on` is NULL for INV-1001 and reads 2026-02-07 for INV-1002.
2. Select **Summarize**. `missing` counts the rows that failed: 11 of 18.
3. Select **DateTime** and set **Text looks like** to the EU date option.
4. Read the preview again. The first row is now a date, and it isn't in Q3.
5. Select **Summarize**. `missing` should fall to a single row. Scroll **DateTime**'s preview to find 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](https://oplema.com/learn/data-preparation/sort) and [filter](https://oplema.com/learn/data-preparation/filter-rows) 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](https://oplema.com/docs/strata/parse/datetime).

## 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.
