Changing year in MySQL date?

To change the year in MySQL date, you need to use DATE_FORMAT() function with UPDATE command. The syntax is as follows.

UPDATE yourTableName
SET yourDateColumnName = DATE_FORMAT(yourDateColumnName ,'yourYearValue-%m-%d');

To understand the above syntax, let us create a table. The query to create a table is as follows −

mysql> create table ChangeYear
   -> (
   -> id int not null auto_increment,
   -> ArrivalTime date,
   -> PRIMARY KEY(id)
   -> );
Query OK, 0 rows affected (0.83 sec)

Insert some records in the table using insert command −

mysql> insert into ChangeYear(ArrivalTime) values(date_add(now(),interval -2 year));
Query OK, 1 row affected, 1 warning (0.20 sec)

mysql> insert into ChangeYear(ArrivalTime) values('2012-10-24');
Query OK, 1 row affected (0.18 sec)

mysql> insert into ChangeYear(ArrivalTime) values('2016-3-21');
Query OK, 1 row affected (0.19 sec)

mysql> insert into ChangeYear(ArrivalTime) values('2015-4-24');
Query OK, 1 row affected (0.20 sec)

mysql> insert into ChangeYear(ArrivalTime) values(curdate());
Query OK, 1 row affected (0.20 sec)

Display all records from the table using select statement. The query is as follows.

mysql> select *from ChangeYear;

The following is the output.

+----+-------------+
| id | ArrivalTime |
+----+-------------+
| 1 | 2017-01-07 |
| 2 | 2012-10-24 |
| 3 | 2016-03-21 |
| 4 | 2015-04-24 |
| 5 | 2019-01-07 |
+----+-------------+
5 rows in set (0.00 sec)

Here is the query to update only the year in the date column. For our example, let us change the year to 2019 −

mysql> update ChangeYear
   -> set ArrivalTime = DATE_FORMAT(ArrivalTime,'2019-%m-%d');
Query OK, 4 rows affected (0.12 sec)
Rows matched − 5 Changed − 4 Warnings − 0

Check all the updated records from the table using select statement. The query is as follows −

mysql> select *from ChangeYear;

The following is the output displaying the updated year 2019, but rest of the date remains the same −

+----+-------------+
| id | ArrivalTime |
+----+-------------+
|  1 | 2019-01-07  |
|  2 | 2019-10-24  |
|  3 | 2019-03-21  |
|  4 | 2019-04-24  |
|  5 | 2019-01-07  |
+----+-------------+
5 rows in set (0.00 sec)