mysqlsqldatedatetime

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).

Solution 
  • 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