MYSQL - Changing year of dates from 2020 to 2011

问题:

I have bunch of dates in format YYYY-MM-DD

But I have all year in 2020-MM-DD

I want to change it to 2011-MM-DD

How can I achieve this ?

回答1:

UPDATE YourTable
    SET YourDateColumn = SUBDATE(YourDateColumn, INTERVAL 9 YEAR);

回答2:

USE ADDDATE(old_date, INTERVAL -9 YEAR)

回答3:

UPDATE YourTable 
SET YourDateColumn = ADDDATE(YourDateColumn, INTERVAL 1 YEAR)
WHERE YourDateColumn >= '2010-01-01'
AND YourDateColumn <= '2010-12-31'; 

If your table down not have an index on the date field, you could get away with this:

UPDATE YourTable 
SET YourDateColumn = ADDDATE(YourDateColumn, INTERVAL 1 YEAR)
WHERE YEAR(YourDateColumn) = 2010;

To fix your date problem with 2020 going to 2021 run this:

UPDATE YourTable 
SET YourDateColumn = ADDDATE(YourDateColumn, INTERVAL -1 YEAR)
WHERE YEAR(YourDateColumn) = 2021;

BTW Since I copied Joe Stefanelli's original code, +1 for him !!!

回答4:

I'd like to add (for further visitors to the question) that you can also "update" positively faster than with ADDDATE statement with the SUBDATE function, I think is reasonably faster, while to ADDDATE took it aprox. 1 hour to affect 2.3 trillion rows SUBDATE made it half the time.

UPDATE Table SET DateTimeField = SUBDATE(DateTimeField, INTERVAL -x YEAR);

Where x is the negative number you want to add as new year value. To be honest I ignore why this happens, but it works.