How to change year of date in mysql with dateformat and variables

Question

I am building a function in MySQL and I need to change the year of a date. I want to use the DATE_FORMAT function.

SET teacherDate = (Select GebDatum from lehrer where Vorname = 'Erika');

SET eingabeDatum = DATE_FORMAT(eingabeDatum, year(teacherDate)-%m-%d);

I want to change the year of the "eingabeDatum" as follows: Month and day should be the same but the year should be the year of the "teacherDate".

Is it possible to use variables in the format part of Date_Format? If yes how is the syntax to use variables or functions like YEAR() in Date_Format.

Answer 1

There are 3 ways that we can do this.

• We can use the functions year(), month() and day() to get the date parts and then use concat to construct the new date.
• When can use DateFormat to get the month and year and then use Date_Add to add the number of years from TeacherDate
• We can use concat to include Year(TeacherDate) in the formatting string in date_format All 3 methods have the same result.

create table lehrer(
Vorname varchar(10),
teacherDate date,
eingabeDatum date
);
insert into lehrer values
('Erika','2022-01-01','2010-04-05'),('Tobias','2022-01-01','2010-04-05'),('John','2022-01-01','2010-04-05');

update lehrer
set eingabeDatum = 
concat(
  year(teacherDate) ,'-',
  month(eingabeDatum),'-',
  day(eingabeDatum)
)
WHERE Vorname = 'Erika';

UPDATE lehrer
SET eingabeDatum = 
DATE_ADD(DATE_FORMAT(eingabeDatum, '0000-%m-%d'), INTERVAL YEAR(teacherDate) YEAR)
WHERE Vorname = 'Tobias';

UPDATE lehrer
SET eingabeDatum = 
    DATE_FORMAT(
        eingabeDatum, 
        concat(
            YEAR(teacherDate),
            '-%m-%d')
        )
WHERE Vorname = 'John';

?

select * from lehrer;

Vorname | teacherDate | eingabeDatum
:------ | :---------- | :-----------
Erika   | 2022-01-01  | 2022-04-05  
Tobias  | 2022-01-01  | 2022-04-05  
John    | 2022-01-01  | 2022-04-05  

db<>fiddle here