Why are my dates sorting wrong? Dates stored as text
Spot a date stored as text in pandas or DuckDB, the kind that sorts, filters, and returns the wrong maximum without any error, and convert it before you use it.
Free course. Your Maestro, an AI, .
Café Altura and its sales file
In this course you work with data from Café Altura, a chain of three coffee shops in Mexico City: Roma, Coyoacán, and Del Valle. The chain doesn't exist and the data is synthetic: we made it for this course, with the same problems real files have.
The first file is ventas_exportadas (exported sales): 29 sales from January to March 2026, exported from the register to a Glossary term. Each row has a receipt number, a date, the shop, and the total in pesos. The date is written the Mexican way, day first: 02/01/2026 is January 2. In the US you'd read that same text as February 1, and that mismatch is a big part of what this course is about.
It looks like a date. To the computer, until someone converts it, it's just ten characters of text. Before you read why that matters, make a prediction.
To complete the exercises, you need foundations in both tools: selecting and filtering a DataFrame, grouping columns in pandas, and writing SELECT, WHERE and GROUP BY in SQL. For calendars you will use WITH and LEFT JOIN, with a small worked preparation in module 3. Tabs change the explanation; the exercises alternate tools and some start without code. You can read with just one foundation, but these are not two independent paths.
Two short Python programs with dates stored as text. Write what you think each one prints and why, then run it.
Sorting four dates
Example 1 of 2
fechas = ["13/01/2026", "28/02/2026", "02/01/2026", "01/03/2026"]print(sorted(fechas))A guess is fine. What you write stays in this browser and waits in your course.
Example 2 of 2, opens once you explain this one
What happened
Text is compared character by character, left to right. In dd/mm/yyyy the first character belongs to the day, so sorting the text sorts by day of the month and leaves the month and the year for later. The result looks sorted, and it's wrong.
In the Café Altura file, the same thing happens with every comparison:
- The minimum date as text is
01/02/2026, February 1. The oldest sale is from January 2. - The maximum as text is
30/01/2026. The most recent sale is from March 28. - The filter
fecha > '15/02/2026'returns 9 sales. There were 13 after February 15.
None of these queries raises an error. That's the problem: text that looks like a date lets you sort, filter, and take a maximum, and hands you a wrong answer without warning. Still seems strange that March 1 comes out before January 2? Your Maestro can walk you through another example.
How to tell what type your column is
Before you sort or filter, check what type the column is. In this course you practice with Glossary term and with DuckDB; pick your tool and the course will show you that one on every page that has both.
data.dtypes shows the type of each column in a DataFrame. In ventas_exportadas, the fecha column shows up as str: text. A column of dates shows up as datetime64.
print(data.dtypes)typeof tells you the type of an expression. In ventas_exportadas, the fecha column is VARCHAR: text. A column of dates is DATE.
SELECT typeof(fecha) FROM ventas_exportadas LIMIT 1;Try it
The query below finds the most recent date while treating the column as text. Add a second column, max_fecha, that finds the same thing after converting the text into a date with strptime(fecha, '%d/%m/%Y')::DATE. Two lessons from now you'll see what each part of the format means; for now, copy it as is.
A good answer has one row with two different values: the text 30/01/2026 and the date 2026-03-28. If your query throws an error, paste it and ask your Maestro.
Your turn
Find the most recent date in ventas_exportadas twice: as text (max_texto) and converted into a date (max_fecha).
Tables: ventas_exportadas (folio, fecha, sucursal, total)
ventas_exportadas · 29 rows
| Column | Type |
|---|---|
| folio Número de ticket del punto de venta. | BIGINT |
| fecha La fecha tal como la exporta el punto de venta: texto en formato día/mes/año (dd/mm/aaaa). | VARCHAR |
| sucursal Roma, Coyoacán o Del Valle. | VARCHAR |
| total Total del ticket en pesos. | BIGINT |
First rows
| folio | fecha | sucursal | total |
|---|---|---|---|
| 1001 | 02/01/2026 | Roma | 460 |
| 1002 | 05/01/2026 | Roma | 120 |
| 1003 | 05/01/2026 | Del Valle | 120 |
Tab indents; Esc then Tab leaves
A converted date is compared as a date: year first, then month, then day. That's why max_fecha really is March 28, and why everything you do with dates from here on starts by converting them.
If your dates already looked strange the moment you opened the CSV in Excel, that's a different problem: Excel can convert or rewrite dates when it opens the file, before you do anything. Another free Maestro course covers that.
Before you go on: six quick questions
Answer what you think, without looking anything up. The first reviews what you just practiced; the other five anticipate new topics and help establish your starting point, and you'll find similar questions at the end of the course. If you miss one, that's exactly what the course teaches.
Check yourself, 1 of 6: You sort these three day/month/year dates as text: 05/01/2026, 13/01/2026, and 02/03/2026. Which one comes first?
Check yourself, 2 of 6: In pandas, you convert a column with pd.to_datetime without telling it the format. What date do you get for the text 05/02/2026?
Check yourself, 3 of 6: The creado_utc column stores a date and a time. You filter March orders with BETWEEN '2026-03-01' AND '2026-03-31'. What happens to an order placed on March 31 at 3:00 p.m.?
Check yourself, 4 of 6: Your sales system stores times in UTC. A sale was recorded as 2026-05-01 02:10:00. Which month does it count in for a shop in Mexico City?
Check yourself, 5 of 6: A shop was closed all of August and has no rows at all for that month. You group its sales by month. What shows up for August?
Check yourself, 6 of 6: Marta was born on September 20, 1990. How old is she on September 1, 2026?
If an answer surprised you, tell your Maestro which one and what you expected.
In the next lesson you'll see where the second question comes from: the same text can be two different dates, and each program picks one.