I have a HUGE database where users type in the date such as 12/25/2015 in the field & submit it via PHP code. This data gets dumped into a MYSQL database. The MYSQL field it gets dumped into is a VARCHAR. Is there anyway I can edit the filed in the MYSQL database to a date field without corrupting the data. I have a test database where I have used ALTER TABLE supervision
CHANGE Test_Date
Test_Date
DATE NULL DEFAULT ‘0000-00-00’ & it does what I want it to do but the existing data all turns to 0000-00-00. Thanks
Since the dates appearently use the custom format “M/D/Y”, MySQL doesn’t recognize them. So you can’t just change the column type. It’s also rather likely that you have broken dates in your database, because a [tt]VARCHAR[/tt] obviously doesn’t provide any data integrity whatsoever. You need to account for that as well.
So I’d take the safe approach: Make an additional(!) [tt]DATE[/tt] column and write a script which iterates over the rows one by one and tries to parse the date from the [tt]VARCHAR[/tt] column. If this succeeds, you can insert the date into the [tt]DATE[/tt] column, otherwise you should display an error message so that the problem can be fixed manually.
[php]<?php
const INPUT_DATE_FORMAT = ‘m/d/Y’;
const MYSQL_DATE_FORMAT = ‘Y-m-d’;
$timeZone = new DateTimeZone(‘UTC’);
// test input
$inputDate = ‘12/25/2015’;
// try to parse the input
$date = DateTime::createFromFormat(INPUT_DATE_FORMAT, $inputDate, $timeZone);
if ($date)
{
/*
* You should make additional plausibility checks to detect garbage dates and
* finally update the DATE column with $date->format(MYSQL_DATE_FORMAT).
*/
}
else
{
// display an error message
echo “Erroneous date $inputDate for ID …”;
}[/php]
After running this you can change the column type.
[php]UPDATE your_table
SET date_field = DATE(STR_TO_DATE(date_field, ‘%m/%d/%Y’))
WHERE DATE(STR_TO_DATE(date_field, ‘%m/%d/%Y’)) <> ‘0000-00-00’;[/php]
Well, after having verified the data is ok they can just drop the old column