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: