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 %:

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.

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 pd.to_datetime and an explicit format, a nonempty incompatible value stops conversion with ValueError. Missing values ("", None) become NaT even without coerce. 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.

NaT means there is no date: it may be an original absence or a value that could not be converted. Keep the original column, count original absences and new failures separately, and inspect them before moving on; a spot-check 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 without a 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. An original NULL remains NULL. If you use read_csv(parse_dates=...), check the type afterward: an incompatible value can leave the entire column as text without raising that error; it does not behave the same way as to_datetime.

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.

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.

Then try a small column containing 28/02/2026, an empty string and 31/02/2026. Preserve the original, identify absences before conversion, use coerce and count only new failures. Distinguish one original absence from one incompatible value, and show 31/02/2026 as the text that failed.