mysql将日期转换年份_mysql将日期转换为当前年份的相同日期

How can I replace the year of a date column with that of the current year?

the following returns NULL

SELECT str_to_date(concat(year(now()), '-',

month(datecolumn), '-' ,day(datecolumn)), '%Y-%M-%D')

FROM TABLE

解决方案

Khalid's answer is correct most of the time. Leap year messes things up! If you run the proposed query where the value of datecol is '2016-02-29' and the CURRENT_DATE is '2017-01-01', for example, you get null.

An alternate way to do this that handles leap year more gracefully is like this:

SELECT DATE_FORMAT(

MAKEDATE(YEAR(CURRENT_DATE()), DAYOFYEAR(datecol)),

'%Y-%m-%d'

) `date`

FROM t

The value of date here would be 2017-03-01.

Edit/clarification: The problem is that changing the year of '2016-02-29' to 2017, for example, produces '2017-02-29', which is not a valid date. Then, running DATE_FORMAT('2017-02-29', '%Y-%m-%d') results in null. A demo of the problem is here:

However, after reviewing my answer I realized that I another problem by using MAKEDATE since any date on a leap year after Feb 28 is days+1 for a "normal" year with 365 days. For example, if datecol = '2016-03-01' and the current year were 2017 then the converted date would be '2017-03-02', not '2017-03-01' as desired. A better approach is as follows:

SELECT

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

FROM t;

This method turns any Feb 29th into the 28th, and otherwise keeps all other dates exactly as you'd expect them. A demo of the solution is here: