Changing column type and date time format in MysQL

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 



Hopefully the information we provide can be useful.

Press ESC to close