How to Alter datetime column to varchar with existing data

BOOKC, this time share about How to Alter datetime column to varchar with existing data

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 

 

Bookc
Admin

Hopefully the information we provide can be useful.

Press ESC to close