MySQL - change many rows of dates

Question

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 rows from 0010 to 2010?

Answer 1

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.

Answer 2

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);

Answer 3

Add 2000 into year.

Answer 4

Hi, try this:

UPDATE table SET field = field + INTERVAL 2000 year;

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

Answer 5

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;