How to update date of datetime field with MySQL?

Update date of datetime field with the help of arithmetic operator minus(-).

The syntax is as follows

update yourTableName
set yourDateTimeColumnName=yourDateTimeColumnName - interval yourValue day
where date(yourDateTimeColumnName)=’yourDateValue’;

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

mysql> create table updateDateDemo
   -> (
   -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   -> ArrivalDate datetime
   -> );
Query OK, 0 rows affected (0.66 sec)

Insert some records in the table using insert command.

The query is as follows

mysql> insert into updateDateDemo(ArrivalDate) values('2011-01-13');
Query OK, 1 row affected (0.19 sec)
mysql> insert into updateDateDemo(ArrivalDate) values('2013-04-21');
Query OK, 1 row affected (0.11 sec)
mysql> insert into updateDateDemo(ArrivalDate) values('2016-12-22');
Query OK, 1 row affected (0.14 sec)
mysql> insert into updateDateDemo(ArrivalDate) values('2017-02-25');
Query OK, 1 row affected (0.27 sec)
mysql> insert into updateDateDemo(ArrivalDate) values('2018-08-19');
Query OK, 1 row affected (0.17 sec)
mysql> insert into updateDateDemo(ArrivalDate) values('2019-02-27');
Query OK, 1 row affected (0.24 sec)

Display all records from the table using select statement.

The query is as follows

mysql> select *from updateDateDemo;

The following is the output

+----+---------------------+
| Id | ArrivalDate         |
+----+---------------------+
| 1  | 2011-01-13 00:00:00 |
| 2  | 2013-04-21 00:00:00 |
| 3  | 2016-12-22 00:00:00 |
| 4  | 2017-02-25 00:00:00 |
| 5  | 2018-08-19 00:00:00 |
| 6  | 2019-02-27 00:00:00 |
+----+---------------------+
6 rows in set (0.00 sec)

Here is the query to update date of datetime field

mysql> update updateDateDemo
   -> set ArrivalDate=ArrivalDate-interval 7 day
   -> where date(ArrivalDate)='2019-02-27';
Query OK, 1 row affected (0.13 sec)
Rows matched: 1 Changed: 1 Warnings: 0

Now check the updated records from the table

mysql> select *from updateDateDemo;

The following is the output

+----+---------------------+
| Id | ArrivalDate         |
+----+---------------------+
| 1  | 2011-01-13 00:00:00 |
| 2  | 2013-04-21 00:00:00 |
| 3  | 2016-12-22 00:00:00 |
| 4  | 2017-02-25 00:00:00 |
| 5  | 2018-08-19 00:00:00 |
| 6  | 2019-02-20 00:00:00 |
+----+---------------------+
6 rows in set (0.00 sec)

Chandu yadav

Updated on 30-Jul-2019 22:30:25

• Related Questions & Answers
• Compare DATE string with string from MySQL DATETIME field?
• Add DATE and TIME fields to get DATETIME field in MySQL?
• How to convert char field to datetime field in MySQL?
• MySQL: update field with Group By?
• Insert datetime into another datetime field in MySQL?
• Add a single day to datetime field with MySQL INTERVAL
• How to update only day portion of MySQL Date?
• How to convert JS date time to MySQL datetime?
• How to update a timestamp field of a MySQL table?
• How to compare DateTime Column with only Date not time in MySQL?
• Extracting only date from datetime field in MySQL and assigning it to PHP variable?
• How to return today's records using DATE from DATETIME Field?
• MySQL queries to update date records with NULL values
• How to cast DATETIME as a DATE in MySQL?
• How to select only MySQL date from datetime column?