MySQL - mass change of data type varchar to datetime in database

Hi, I'm noob and I have the date stored in the database as a varchar data type and I would like to convert it to datetime.

First I would need to use SQL update to reformat the whole column with varchar datetime format '%d.%m.%Y' eg 5.5.2020 to '%Y-%m-%d' ie. 2020-05-05.

Then I change that column from varchar to date.

Thanks

REPLY

Hi,

to reformat an SQL database column from data type varchar to datetime I would do as follows:

UPDATE table SET Datum =
DATE_FORMAT(STR_TO_DATE(Datum, '%d.%m.%Y'), '%Y-%m-%d')
WHERE Datum LIKE '__.__.____'

And then change datetype of Date column to datetime.