How to update only day portion of MySQL Date?

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;

Output

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 −

Output

This will produce the following output −

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

Sharon Christine

Published on 10-Jul-2019 13:43:59

• Related Questions & Answers
• MySQL query to update only month in date?
• MySQL query to extract only the day instead of entire date
• How to get only Date portion from DateTime object in C#?
• Compare only day and month with date field in MySQL?
• How to update date of datetime field with MySQL?
• How to update the date format in MySQL?
• How to update a MySQL date type column?
• How to add a day to the date in MySQL?
• How to add 1 day to the date in MySQL?
• How to display first day and last day of the month from date records in MySQL?
• MongoDB query to search date records using only Month and Day
• How to select only MySQL date from datetime column?
• How to use date command in day to day practical usage
• Format MySQL date and convert to year-month-day
• Update only the int in MySQL Field