Changing year in mysql date

Question

I have a bunch of dates in our database stored in the standard mysql date type.

How can I covert a year to 2013, regardless of original date.

So if a date is 2009-01-01 it would be 2013-01-01, but if it's 2012-01-04, it'd convert to 2013-01-14.

I figured it'd be simple and obvious, but I couldn't figure it out =/

Answer 1

That's simple:

for DATETIME:

UPDATE table_name SET date_col=DATE_FORMAT(date_col,'2013-%m-%d %T'); 

for DATE:

UPDATE table_name SET date_col=DATE_FORMAT(date_col,'2013-%m-%d'); 

Answer 2

The problem with the current answers is that none of them take leap year into account. If you take the date '2016-02-29' and convert it to the year 2013 through concatenation, you get '2013-02-29', which is not a valid date. If you run DATE_FORMAT('2013-02-29', '%Y-%m-%d') the result is null. See an example here:

http://sqlfiddle.com/#!9/c5358/11

A better way to change the year is to use DATE_ADD since it accounts for daylight savings. For example:

SELECT DATE_FORMAT(DATE_ADD(datecol, INTERVAL (YEAR(CURRENT_DATE()) - YEAR(datecol)) YEAR), '%Y-%m-%d') `date` FROM t; 

You could substitute CURRENT_DATE() with '2013-01-01' if you still wanted to convert all dates to 2013 instead of the current year. An example of this solution is here:

http://sqlfiddle.com/#!9/c5358/12