Covert all date data format from VARCHAR to DATE in any MySQL table

I am working on a project which was majorly developed by any otehr developer. My work is to update every module and add some functionality. This is a financial application and already have huge amount data in the database and I have to work on existing data.

I noticed that all the Dates are stored into MySQL tables in VARCHAR format instead of DATE format. So, basically I can not implement any date related filetering feature.

Now, my primary task is to convert all VARCHAR date to actual DATE format.

Here’s a method I followed to achieve this:

Assuming my varchar date column is named date_column and my table is named my_table, I can follow these steps:

1. Add a New Date Column: First, add a new date column to my table.

   ALTER TABLE my_table ADD new_date_column DATE;

2. Update New Date Column: Update the newly added date column using the STR_TO_DATE function to convert the varchar dates to date format.

   UPDATE my_table SET new_date_column = STR_TO_DATE(date_column, ‘my_date_format’);

   Replace ‘my_date_format’ with the format of the varchar dates in my column. For example, if my dates are in the format ‘YYYY-MM-DD’, use ‘%Y-%m-%d’.

3. Drop Old Date Column: If I am confident that the new date column contains the correct data, I can drop the old varchar date column.

   ALTER TABLE my_table DROP COLUMN date_column;

4. Rename New Date Column: Finally, rename the new date column to the original column name.

   ALTER TABLE my_table CHANGE new_date_column date_column DATE;

Note: Anything of you will follow this process, make sure to take a backup of my data before making such changes to your database. Incorrectly manipulating your database structure can lead to data loss. If possible, it’s recommended to keep dates in the appropriate date or datetime format rather than varchar to avoid these kinds of issues.

Also, be aware that converting varchar data to date format directly in the database can be resource-intensive, especially if you have a large amount of data. It’s usually better to clean and format data before inserting it into the database in the correct format.