Dates changed to 0000-00-00

Question

Previously, on my table (~81000 rows), I have had date and time fields set as VARCHAR instead of date/time. All those dates were of the type MM/DD/YY. I changed the type from VARCHAR to DATE and TIME respectively and most of my dates, which had DD >= 13 to 0000-00-00.

Now, I am left with a backup I took 3 months ago (~77400 rows) and then the newer ones. Is there any way that I can restore the older dates?

The way I see it, I'll have to try updating the dates using the older .SQL file for those 77400 rows and after that will have to write a script to check and increment date with each passing 24 hour span in the TIME column.

I didn't have a backup taken after 12th April and didn't consider taking one while altering the database. :(

EDIT

mysql Ver 14.14 Distrib 5.1.61, for debian-linux-gnu (i486) using readline 6.1

CREATE TABLE `latest` (
  `msg` VARCHAR(280) NOT NULL,
  `id` VARCHAR(20) NOT NULL,
  `ctg` VARCHAR(10) NOT NULL,
  `date` VARCHAR(50) NOT NULL COMMENT 'date_format( %m/%d/%y )',
  `time` VARCHAR(50) NOT NULL COMMENT 'date_format( %H:%i:%s )',
  `fid` BIGINT(20) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`fid`)
) COLLATE='utf8_general_ci' ENGINE=InnoDB ROW_FORMAT=DEFAULT AUTO_INCREMENT=80685

I changed the table code from that to this:

CREATE TABLE `latest` (
  `msg` VARCHAR(280) NOT NULL,
  `id` VARCHAR(20) NOT NULL,
  `ctg` VARCHAR(10) NOT NULL,
  `date` DATE NOT NULL COMMENT 'date_format( %m/%d/%y )',
  `time` TIME NOT NULL COMMENT 'date_format( %H:%i:%s )',
  `fid` BIGINT(20) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`fid`)
) COLLATE='utf8_general_ci' ENGINE=InnoDB ROW_FORMAT=DEFAULT AUTO_INCREMENT=80685

Answer 1

Well, usually I use the following approach: instead of altering a live column type (if it's not something really trivial), I add another column of a new type, then set all the values in it (more on it below), then delete the old column after doing all that (and checking that all works ok), using its name as a name for a new column.

Perhaps this approach can still be used. To convert old values use something like this:

UPDATE my_table 
   SET datetime_column = STR_TO_DATE(varchar_column, '%d-%m-%Y')

Please take note that actual format literal might be different in your case; consult the manual and test it for some exact value to be sure.

Answer 2

Its not possible to restore the older dates. If you change the type of a field in SQL, the server (mySQL?) tries to find the right value by itself. if this fails, it will insert the default value. The older values are lost.

The Solution would be to write a script (PHP?) which will parse all your data and then save it correctly in the database.

The strtotime and date function of PHP could help you a lot. http://de.php.net/manual/de/function.strtotime.php & http://php.net/manual/de/function.date.php

For Example:

date("Y-m-d H:i:s", strtotime(YOUR VCHAR))