Why are my dates sorting wrong? Dates stored as text

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

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

ordenar.py

fechas = ["13/01/2026", "28/02/2026", "02/01/2026", "01/03/2026"]
print(sorted(fechas))

What order do you think they come out in?

Why?

A guess is fine: it's kept as you wrote it, so you can compare. Only you and your Maestro see your predictions.

Example 2 of 2, opens once you explain this one

Make your prediction

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

Python

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.

SQL

SELECT typeof(fecha) FROM ventas_exportadas LIMIT 1;

typeof and other utility functions (DuckDB documentation)

DataFrame.dtypes (pandas documentation)

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

Your query (SQL)

Every run starts from fresh copies of the course's tables, so try anything. Tab indents; to leave the editor, press Escape, then Tab.

Ready to run your query.

Run it yourself

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 (currently in Spanish).

Your CSV looks weird: broken accents, commas, zeros, and dates (in Spanish)

Before you go on: six quick questions

Answer what you think, without looking anything up. They don't count for anything: they let you and your Maestro see where you're starting from, 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: 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: 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: 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: 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: 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: 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.