MySQL - change many rows of dates

I have a fairly large number of rows where a datetime field is stored such as:

0010-01-03 00:00:00

But, I need it to be:

2010-01-03 00:00:00

Any suggestions on how to mass change row开发者_Go百科s from 0010 to 2010?

UPDATE table SET field = field + INTERVAL 2000 year;

Take a look on MySQL Date and Time Functions . =]

You could do something like this :

UPDATE table SET date = date_add(date, INTERVAL 2000 YEAR) WHERE YEAR(date) < 11;

If you have years like '0099' :

UPDATE table SET date = date_add(date, INTERVAL 1900 YEAR) WHERE YEAR(date) > 10 AND YEAR(date) < 100;

UPDATE table
SET field = '2010-01-03 00:00:00'
WHERE field = '0010-01-03 00:00:00'

Without more information, this is the only real answer I can give.

Add 2000 into year.

UPDATE mytable SET mytimefield = mytimefield + INTERVAL '2000 years' where mytimefield < now() - INTERVAL '15 YEAR';

Not sure if that's the exact syntax for MySQL. It might be

UPDATE mytable SET mytimefield = DATE_ADD(mytimefield, INTERVAL 2000 YEAR) mytimefield < DATE_SUB(now(), INTERVAL 15 YEAR);

add 2000 years to values with a year lower than 2000, that should definitely fix it