How to update only day portion of MySQL Date?

Sharon Christine

Answered 22h 44m ago

Let us first create a table −

mysql> create table DemoTable
   -> (
   -> AdmissionDate date
   -> );
Query OK, 0 rows affected (1.38 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable values('2019-05-12');
Query OK, 1 row affected (0.52 sec)

mysql> insert into DemoTable values('2019-05-18');
Query OK, 1 row affected (0.19 sec)

mysql> insert into DemoTable values('2019-04-19');
Query OK, 1 row affected (0.42 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable;

This will produce the following output −

+---------------+
| AdmissionDate |
+---------------+
| 2019-05-12    |
| 2019-05-18    |
| 2019-04-19    |
+---------------+
3 rows in set (0.00 sec)

Following is the query to update only day portion of MySQL Date. Here, we have set the same day to all the dates in the column −

mysql> update DemoTable
   -> set AdmissionDate=date_format(AdmissionDate,'%Y-%m-25') ;
Query OK, 3 rows affected (0.44 sec)
Rows matched: 3 Changed: 3 Warnings: 0

Let us check all the records once again −

This will produce the following output −

+---------------+
| AdmissionDate |
+---------------+
| 2019-05-25    |
| 2019-05-25    |
| 2019-04-25    |
+---------------+
3 rows in set (0.00 sec)