I have three column mysql table ID (int) Date (Varchar) (data in dd-mm-yyyy) Logdate (Date) (Currently empty)
Is there any query which can fetch date and update the Logdate with mysql date format (yyyy-mm-dd)
See Question&Answers more detail:osI have three column mysql table ID (int) Date (Varchar) (data in dd-mm-yyyy) Logdate (Date) (Currently empty)
Is there any query which can fetch date and update the Logdate with mysql date format (yyyy-mm-dd)
See Question&Answers more detail:osYou can use the STR_TO_DATE
function to convert the string to a Date
value. You have to specify the format of the date string you provided. In your case it would be '%d-%m-%Y'
. For testing purposes you can run the following queries to check if it works:
mysql> EXPLAIN Dummy;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| ID | int(11) | YES | | NULL | |
| oldDate | varchar(50) | YES | | NULL | |
| logdate | date | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM Dummy;
+------+------------+------------+
| ID | oldDate | logdate |
+------+------------+------------+
| 1 | 24-12-2019 | 2000-01-01 |
| 1 | 04-09-2017 | 2000-01-01 |
| 1 | 21-02-2019 | 2000-01-01 |
+------+------------+------------+
3 rows in set (0.00 sec)
mysql> SELECT oldDate, STR_TO_DATE(oldDate, '%d-%m-%Y') FROM Dummy;
+------------+----------------------------------+
| oldDate | STR_TO_DATE(oldDate, '%d-%m-%Y') |
+------------+----------------------------------+
| 24-12-2019 | 2019-12-24 |
| 04-09-2017 | 2017-09-04 |
| 21-02-2019 | 2019-02-21 |
+------------+----------------------------------+
3 rows in set (0.00 sec)
You can then run an UPDATE
query to update the values in the logdate
column.
mysql> UPDATE Dummy SET logdate = STR_TO_DATE(oldDate, '%d-%m-%Y');
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> SELECT * FROM Dummy;
+------+------------+------------+
| ID | oldDate | logdate |
+------+------------+------------+
| 1 | 24-12-2019 | 2019-12-24 |
| 1 | 04-09-2017 | 2017-09-04 |
| 1 | 21-02-2019 | 2019-02-21 |
+------+------------+------------+
3 rows in set (0.00 sec)