Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL alter table and convert data from text to datetime

I have a table with a column date but it is stored as text.

Now I need to extract data based on date and I'm thinking I need to alter the column type to datetime, but how can I do that without losing data? My text records are in format dd-MM-YYYY hh:mm

If I just change the column type the data I lose all data (it is filled with zeros).

like image 398
João Correia Avatar asked Nov 19 '13 14:11

João Correia


1 Answers

UPDATE `table`
SET `column` = STR_TO_DATE(`column`,'%d-%M-%Y %h:%i')

Just change the format to what you have,in case that is not correct.

Formats

SQL fiddle

like image 185
Mihai Avatar answered Sep 29 '22 06:09

Mihai