Converting all data in a Varchar column to a date format

I'm working on a table with a column, 'Expiry Date', as a varchar with all data formatted as DD/MM/YYYY.

The creator of the table has used the wrong type for this expiry date column and now the client needs to filter and show all records before and after the current date as the time. This means the type needs to be changed to date or datetime type to be able to use the CURDATE() function.

However, the current format of the values does not satisfy and wont allow the type to change unless the format is changed to YYYY-MM-DD (or similar).

Is there any way to mass format the values in this column and this column alone as there are thousands of entries and formatting one by one would be extremely time consuming.

Solution

Let me assume that you are using MySQL.

Perhaps the simplest method is to add a generated column that is a date:

alter table t add column expiry_date_date as
    (str_to_date(expiry_date, '%d/%m/%Y'));

You can also fix the data:

update t
    set expiry_date = str_to_date(expiry_date, '%d/%m/%Y');

This will implicitly convert the result of str_to_date() to a date, which will be in the YYYY-MM-DD format.

More importantly, you can then do:

alter table t modify column expiry_date date;

Here is a db<>fiddle.

You can do similar operations in other databases, but the exact code is a bit different.