CODEWITHSUNDEEP
mysql
Todd M
Todd M

Reputation: 1010

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

Upvotes: 0

Views: 665

Answers (6)

patrick
patrick

Reputation: 11721

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

Upvotes: 0

codea
codea

Reputation: 1252

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;

Upvotes: 1

Paul Tomblin
Paul Tomblin

Reputation: 182782

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

Upvotes: 0

Gadonski
Gadonski

Reputation: 3330

UPDATE table SET field = field + INTERVAL 2000 year;

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

Upvotes: 2

Svisstack
Svisstack

Reputation: 16616

Add 2000 into year.

Upvotes: 0

Tarka
Tarka

Reputation: 4043

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.

Upvotes: 0

Related Questions