How to Convert "TEXT" to "Date" in Mysql table

I wrongly open a "date" value as a "text" and now I cant Order by date correctly in "MYSQL" Table..

how can I convert date that "text" to "date" format?

it is format like: "07 April 2021 Wednesday" ....


1 answer

  • answered 2021-05-14 16:53 Steverst1

    Better way is to update the column to date by STR_TO_DATE in order to not lost data when alter it, then alter the column and set DATE as datatype for it.

    UPDATE `table` SET `date` = STR_TO_DATE(`date`, "%d %M %Y %W");
    ALTER TABLE `table` MODIFY `date` date

    Format I used!

    07 April 2021 Wednesday
    %d <- 07
    %M <- April
    %Y <- 2021
    %W <- Wednesday*/