Convert VARCHAR dates to a proper DATE column

Updated August 2026 · First published 2006 by David J. Lake

Part of the classic SQL Help series first published on this domain by David J. Lake (guelphdad). Rewritten from scratch and updated for MySQL 8 — same address, modern SQL.

Dates end up stored as text for one reason: someone wanted them displayed as 23/06/2002 rather than 2002-06-23, and storing the display format seemed the obvious way. It costs you everything the database knows about dates — sorting breaks ('01/12/2025' sorts before '02/01/2020'), date arithmetic and range queries stop working, and every date function is off the table.

The rule: store dates as DATE/DATETIME, format them at display time with DATE_FORMAT(order_date, '%d/%m/%Y'). Storage format and display format are different jobs.

STR_TO_DATE: text in, date out

STR_TO_DATE() is DATE_FORMAT() in reverse — you describe the text's layout with the same % specifiers, and it returns a real date:

SELECT STR_TO_DATE('23/06/2002', '%d/%m/%Y');   -- 2002-06-23
SELECT STR_TO_DATE('06-23-02',   '%m-%d-%y');   -- 2002-06-23
SELECT STR_TO_DATE('1/4/02',     '%d/%m/%y');   -- 2002-04-01 (single digits are fine)

The specifiers you'll use most: %d day, %m month, %Y four-digit year, %y two-digit year (69–99 → 19xx, 00–68 → 20xx — check that assumption against your data!), plus %H:%i:%s for times.

The safe migration workflow

Never convert in place in one shot. Add a new column, convert into it, inspect, then swap — you keep the original text until you're sure:

-- 1. add the real column
ALTER TABLE orders ADD COLUMN order_date_new DATE;

-- 2. convert
UPDATE orders
SET order_date_new = STR_TO_DATE(order_date, '%d/%m/%Y');

-- 3. hunt for failures: rows whose text didn't match the pattern
SELECT order_id, order_date
FROM orders
WHERE order_date_new IS NULL AND order_date IS NOT NULL;

-- 4. fix stragglers (different format? garbage?), then swap
ALTER TABLE orders DROP COLUMN order_date,
                   RENAME COLUMN order_date_new TO order_date;

Strict mode note: with default MySQL 8 settings, an unparseable string makes the UPDATE fail with an error rather than quietly writing NULL. That's a feature — but if you'd rather sweep the failures up afterwards as in step 3, wrap the conversion: SET order_date_new = IF(STR_TO_DATE(order_date,'%d/%m/%Y') IS NOT NULL, STR_TO_DATE(order_date,'%d/%m/%Y'), NULL), or temporarily SET SESSION sql_mode = '' for the migration session.

Messy data: the SUBSTRING_INDEX trick

Real legacy columns are rarely one clean format — '23/06/02' sits next to '1/4/02'. Because STR_TO_DATE copes with missing leading zeros, it handles most of that. But when you need to take a delimited string apart yourself — mixed separators, rearranged parts, dates embedded in reference codes — SUBSTRING_INDEX(str, delim, n) is the tool. It returns everything before the nth occurrence of the delimiter; negative n counts from the end:

SELECT SUBSTRING_INDEX('23/06/02', '/',  1);                        -- '23'  (day)
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('23/06/02','/',2), '/', -1); -- '06'  (month)
SELECT SUBSTRING_INDEX('23/06/02', '/', -1);                        -- '02'  (year)

Unlike position-based SUBSTRING(), this doesn't care how many digits each part has. Reassemble in ISO order and cast:

SELECT CAST(CONCAT(
         '20', SUBSTRING_INDEX(olddate,'/',-1), '-',
         SUBSTRING_INDEX(SUBSTRING_INDEX(olddate,'/',2),'/',-1), '-',
         SUBSTRING_INDEX(olddate,'/',1)
       ) AS DATE) AS newdate
FROM ourdates;

It's the same dissection the 2006 version of this page taught — still handy, just demoted from "the way" to "the fallback for data STR_TO_DATE can't describe".