Changing year in mysql date

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


UPDATE tableName
SET    dateColumn = dateColumn + INTERVAL 4 YEAR
  • SQLFiddle Demo

other way is to concatenate it,

UPDATE Table1
SET    DateColumn = CONCAT(YEAR(CURDATE()), '-', DATE_FORMAT(dateColumn, '%m-%d'))
  • SQLFiddle Demo

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

Tags:

Mysql

Date

Recent Posts