How do I convert text to dates in pandas and DuckDB?

A format is an instruction

You already know ventas_exportadas puts the day first. Now tell the program. A format describes the text piece by piece, with codes that start with %:

Code	What it reads	Example
`%d`	day of the month, two digits	`05`
`%m`	month, two digits	`02`
`%Y`	year, four digits	`2026`
`%H`	hour from 00 to 23	`20`
`%M`	minute	`44`
`%S`	second	`40`

Anything that isn't a code is matched as is. 05/02/2026 is described as %d/%m/%Y: day, a slash, month, a slash, year. pandas and DuckDB use the same codes for these pieces.

pd.to_datetime takes the format in format:

Python

data["fecha"] = pd.to_datetime(data["fecha"], format="%d/%m/%Y")

The column goes from str to datetime64.

strptime takes the text and the format, and returns a TIMESTAMP. Since there's no time here, turn it into a DATE with ::DATE:

SQL

SELECT strptime(fecha, '%d/%m/%Y')::DATE AS fecha
FROM ventas_exportadas;

Cleaning up the fecha column in ventas_exportadas

Worked example · 5 steps

Convert the fecha column from text to a date and check that the conversion came out right.

Work out the next step yourself, then open it to compare.

What doesn't fit the format

A real file almost always has some value that doesn't fit: a day that doesn't exist, a date written another way, an empty cell. With an explicit format, pandas stops at the first one with a ValueError. Sometimes that's what you want. Other times you'd rather convert what you can and look at the rest separately. Predict what errors="coerce" does.

Five strings and an explicit format. Write what you think it prints and why.

Five values, one of each kind

coerce.py

import pandas as pd

fechas = pd.Series(["28/02/2026", "31/02/2026", "1/3/2026", "2026-03-05", ""])
print(pd.to_datetime(fechas, format="%d/%m/%Y", errors="coerce"))

What do you think ends up in each position?

Why?

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

Make your prediction

A NaT isn't an error you can ignore: it's a row that lost its date. After converting with coerce, count how many you got and look at them before moving on; a of those rows tells you whether it's an empty cell or a different format you need to convert separately. Not sure what to do with your rows that lost their date? Ask your Maestro.

DuckDB gives you both options as two functions: strptime stops with an error at the first value that doesn't fit (with 31/02/2026 it says Date out of range), and try_strptime returns NULL instead.

Warning: dayfirst is not a format

pandas also accepts dayfirst=True. The pandas documentation warns that it isn't strict: it prefers to read the day first, but doesn't guarantee it. An explicit format isn't a preference, it's an instruction.

Try it

Now with less help. The query adds up sales by day, but it groups and sorts by the text column. Change it so it converts fecha with its format, calls the result dia, and sorts by the real date.

A good answer has 22 rows, one for each day with sales, from 2026-01-02 to 2026-03-28 in order. If you converted the date and the days are still out of order, tell your Maestro what you put in GROUP BY and ORDER BY.

Your turn

Add up total sales by day, with the converted date (call it dia) in chronological order.

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

Try it on your own

Now in pandas, from scratch: convert the fecha column in ventas_exportadas, then print the earliest date, the latest date, and how many rows ended up without a date. A good answer prints January 2 and March 28, 2026, and 0 rows without a date.

Convert the fecha column with its explicit format. Print the earliest date, the latest date, and how many rows ended up without a date.

Your code (Python)

Run your code to see what it prints and the value of its last expression. Every run starts fresh. Ctrl+Enter runs it. Tab indents; to leave the editor, press Escape, then Tab.

Ready to run your code.

Run your code to see its output here.

Data: ventas-exportadas, 29 rows

Enter saves a cell and Escape leaves it as it was. Leave a cell empty for a missing value.

The exercise's data, "ventas-exportadas". Your edits run with your code.
Row					Remove
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

Page 1 of 2

Run it yourself

Reflect: Think of a file with dates that you use at work. What format do the dates come in, and how would you check that against the data before converting them?

Only you and your Maestro read this. It's kept on your account and deleted with it.

Write your answer

pandas.to_datetime (pandas documentation)

Date format functions: strftime and strptime (DuckDB documentation)