Loading

mysql database date format

Mark Tabor

i have created a database and unfortenatley it type is set to varchar not my between function of mysql get wrong records so i want to keep the date format to be changed from 08/24/2018 t0 2018-08-24 without losing data how can i do that once i change the datatype mysql mark all records to 00:00

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Mark Tabor8y ago
I just create a new column with datatype as date and then simply transfer the date into new date column with correct format

update `table` set `new_col` = str_to_date( `old_col`, '%m/%d/%Y' ) ;

That did the job thanks ali tuncer
ali tuncer8y ago
create a new column of date type, then update the records, you can convert varchar to date..
https://stackoverflow.com/questions/5201383/how-to-convert-a-string-to-date-in-mysql

or you can check these links, you should take a backup before changing things:
https://stackoverflow.com/questions/1908394/mysql-using-a-string-column-with-date-text-as-a-date-field
https://stackoverflow.com/questions/1356866/how-do-i-change-the-data-type-for-a-column-in-mysql