Last updated on April 9th, 2022 at 05:58 pm
It is very rare to find this kind of tutorial in the web that will assist developers to copy all the data from a date column of type VARCHAR to a new column of type DATE in MySQL without having too much complex modification on your base code.
For example, if we need a report from the MySQL table between 05/01/2012 & 05/03/2012 there are chances in which dates like 05/01/2011 or 05/01/2010 may also get added even if those dates are not part of the ones we have selected , This is because MySQL is unable to sort the dates since it is of type VARCHAR or any other data type (other than the default DATE type). In order to fix this issue the column where dates are getting added should be date type.
I will explain here an easy method to convert all the dates to MySQL format.
Step 1 : Take a backup of the DB
Step 2 : Alter the table and create a new date column say ‘New_Date’, after the original Old_Date column
ALTER TABLE `test_table` ADD `New_Date` DATE NULL AFTER Old_Date;
Step 3: Update the column with the date format
UPDATE test_table SET New_Date = STR_TO_DATE(Old_Date, '%m/%d/%Y');
As you can see from the screenshot above, Old_Date refers to my existing column, %m/%d/%Y is the format in which the column is having its date.
For example if you have an existing old Date column value as 05/01/2012, after running this query you will have a value under New_Date as 2012-05-01.
Step 4: If you have client date picker format it accordingly ie: 2012-05-01
Step 5: In your report generation page modify the date value using your server side script’s explode / split function(if required) ie: it should look like 2012-05-01. Update the MySQL query to select date from the new date column, in this case it is New_Date
admin