Question

MySQL - Changing year of dates from 2020 to 2011?

Answer 1

You can change year of dates from 2020 to 2011 using SUBDATE() with INTERVAL of 9 year because there is a difference of 9 years between 2020 to 2011.

The syntax is as follows:

UPDATE yourTableName

SET yourDateColumnName=SUBDATE(yourDateColumnName,INTERVAL 9 YEAR);

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

mysql> create table ChangeYearFrom2020To2011

-> (

-> Id int NOT NULL AUTO_INCREMENT,

-> ExpiryDate date,

-> PRIMARY KEY(Id)

-> );

Query OK, 0 rows affected (0.67 sec)

Insert some records in the table using insert command. The query to insert record is as follows:

mysql> insert into ChangeYearFrom2020To2011(ExpiryDate) values('2020-09-12');

Query OK, 1 row affected (0.19 sec)

mysql> insert into ChangeYearFrom2020To2011(ExpiryDate) values('2020-12-21');

Query OK, 1 row affected (0.17 sec)

mysql> insert into ChangeYearFrom2020To2011(ExpiryDate) values('2020-01-29');

Query OK, 1 row affected (0.12 sec)

mysql> insert into ChangeYearFrom2020To2011(ExpiryDate) values('2020-06-30');

Query OK, 1 row affected (0.19 sec)

mysql> insert into ChangeYearFrom2020To2011(ExpiryDate) values('2020-12-31');

Query OK, 1 row affected (0.13 sec)

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

mysql> select *from ChangeYearFrom2020To2011;

The following is the output:

+----+------------+

| Id | ExpiryDate |

+----+------------+

| 1 | 2020-09-12 |

| 2 | 2020-12-21 |

| 3 | 2020-01-29 |

| 4 | 2020-06-30 |

| 5 | 2020-12-31 |

+----+------------+

5 rows in set (0.00 sec)

Here is the query to change only year from 2020 to 2011:

mysql> update ChangeYearFrom2020To2011

-> set ExpiryDate=subdate(ExpiryDate,interval 9 year);

Query OK, 5 rows affected (0.20 sec)

Rows matched: 5 Changed: 5 Warnings: 0

We will now check all the records of the table once again. The query is as follows:

mysql> select *from ChangeYearFrom2020To2011;

The following is the output:

+----+------------+

| Id | ExpiryDate |

+----+------------+

| 1 | 2011-09-12 |

| 2 | 2011-12-21 |

| 3 | 2011-01-29 |

| 4 | 2011-06-30 |

| 5 | 2011-12-31 |

+----+------------+

5 rows in set (0.00 sec)