MySQL: Changing column type and date time format

Question

My DB currently have 'date' column setup as varchar(20) and I date is formatted like this:

1/13/2015 20:00

I would like to run an update on my DB to change column type to datetime and change format of my current date to something more typical like

yyyy-mm-dd hh:mi

Can this be done in MySQL?

Answer 1

Use STR_TO_DATE function

This is the inverse of the DATE_FORMAT() function. It takes a string str and a format string format. STR_TO_DATE() returns a DATETIME value if the format string contains both date and time parts, or a DATE or TIME value if the string contains only date or time parts. If the date, time, or datetime value extracted from str is illegal, STR_TO_DATE() returns NULL and produces a warning.

To view the converted dates

select str_to_date(date_column, '%m/%d/%Y %h:%i') 
from tablename

Make sure everything is fine then run update statement

update tablename set date_column = str_to_date(date_column, '%m/%d/%Y %h:%i')

Its better to store datetime data in datetime datatype

Alter table tablename modify column date_column datetime