Convert varchar to date in MySQL?

You can use date_format() to convert varchar to date. The syntax is as follows −

SELECT DATE_FORMAT(STR_TO_DATE(yourColumnName, 'yourFormatSpecifier'), 'yourDateFormatSpecifier') as anyVariableName from yourTableName;

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

mysql> create table VarcharToDate
   -> (
   -> Id int NOT NULL AUTO_INCREMENT,
   -> Created_Time varchar(100),
   -> PRIMARY KEY(Id)
   -> );
Query OK, 0 rows affected (1.10 sec)

Insert some records in the table using insert command. The query is as follows −

mysql> insert into VarcharToDate(Created_Time) values('12/1/2016');
Query OK, 1 row affected (0.14 sec)

mysql> insert into VarcharToDate(Created_Time) values('14/3/2017');
Query OK, 1 row affected (0.18 sec)

mysql> insert into VarcharToDate(Created_Time) values('15/3/2018');
Query OK, 1 row affected (0.21 sec)

mysql> insert into VarcharToDate(Created_Time) values('19/5/2011');
Query OK, 1 row affected (0.18 sec)

mysql> insert into
VarcharToDate(Created_Time) values('19/8/2019');
Query OK, 1 row affected (0.15 sec)

mysql> insert into VarcharToDate(Created_Time) values('21/11/2020');
Query OK, 1 row affected (0.23 sec)

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

mysql> select *from VarcharToDate;
+----+--------------+
| Id | Created_Time |
+----+--------------+
|  1 | 12/1/2016    |
|  2 | 14/3/2017    |
|  3 | 15/3/2018    |
|  4 | 19/5/2011    |
|  5 | 19/8/2019    |
|  6 | 21/11/2020   |
+----+--------------+
6 rows in set (0.00 sec)

Here is the query to convert varchar to date. First, you need to use str_to_date() function to convert into date. After that use date_format() to give the actual date −

mysql> select date_format(str_to_date(Created_Time, '%d/%m/%Y'), '%Y-%m-%d') as Date from VarcharToDate;

The following is the output −

+------------+
| Date       |
+------------+
| 2016-01-12 |
| 2017-03-14 |
| 2018-03-15 |
| 2011-05-19 |
| 2019-08-19 |
| 2020-11-21 |
+------------+
6 rows in set (0.00 sec)

Arjun Thakur

Published on 01-Feb-2019 10:18:48