Mysql converts a date to the same date of the current year

Question

How to replace the year column of a date column with the current year?

the following NULL values ​​are returned

 SELECT str_to_date(concat(year(now()), '-', month(datecolumn), '-' ,day(datecolumn)), '%Y-%M-%D') FROM TABLE 

Answer 1

Khalid's answer is in most cases correct. Leap year ruined everything! If you run the proposed query where the datecol value is '2016-02-29' and CURRENT_DATE is '2017-01-01', for example, you get null .

An alternative way to do this, which handles a leap year more gracefully, is as follows:

 SELECT DATE_FORMAT( MAKEDATE(YEAR(CURRENT_DATE()), DAYOFYEAR(datecol)), '%Y-%m-%d' ) `date` FROM t 

The date value here will be 2017-03-01.

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

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

However, after considering my answer, I realized that I have a different problem using MAKEDATE, since any date in a leap year after February 28 is + 1 days for a “normal” year with 365 days. For example, if datecol = '2016-03-01' and the current year was 2017, then the converted date will be '2017-03-02', and 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 February 29th into the 28th, and otherwise saves all other dates exactly as you would expect from them. The demo version of the solution is here:

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

Answer 2

You can do it

 SELECT CONCAT(YEAR(CURRENT_DATE()),RIGHT(datecol,15)) `date` FROM t 

Demo

Or just for the date

 SELECT DATE_FORMAT( CONCAT(YEAR(CURRENT_DATE()),RIGHT(datecol,15)) ,'%Y-%m-%d') `date` FROM t 

Demo 2