While migrating data from a non-y2k compliant system, I found myself with hundreds and hundreds of rows with this date error, instead of it being 12-20-1997, the old system was storing the dates like this 12-20-97, apparently not a problem for a web based system yet it represented a mayor issue when trying to pull out records with dates beyond the year 2000, when I tried to query for the year 2010 records I left out all of those records which date had the error mentioned above, so any record with a date like this 03-14-10 would be left out of the results of the query.
14+ years of data can’t possibly be fixed manually in an efficient way, so looking into the list of mysql functions to fix this I found this awesome function REPLACE, the code basically is as follows
UPDATE `patients`
SET `reg_date` = replace(reg_date,'11','2011')
WHERE reg_date LIKE '%-%-11'
A combination of UPDATE, REPLACE and LIKE can get the job done in seconds! With a small php script you can also loop it, having a date range inside the loop. Something like this.
$start_year = 1;
$end_year = 11;
for ($i= $start_year; $i <= $end_year ; $i++)
{
$query ="UPDATE`patients`
SET `reg_date` = REPLACE(reg_date,'11','2011')
WHERE `reg_date` LIKE '%-%-11'";
mysql_query($query, $connection)or die(mysql_error());
//Assuming a $connection variable was previously declared
}
There you have it! A nifty example of mysql’s REPLACE function.