Syntax error Date format to convert dates like Jan 2017, May 2018 to complete date in MySQL

Date format to convert dates like Jan 2017, May 2018 to complete date in MySQL

For this, use STR_TO_DATE() along with DATE_FORMAT(). Let us first create a table −

mysql> create table DemoTable1985
   (
   DueDate varchar(20)
   );
Query OK, 0 rows affected (0.00 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable1985 values('Jan 2017');
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1985 values('May 2018');
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1985 values('Aug 2015');
Query OK, 1 row affected (0.00 sec)

Display all records from the table using select statement −

mysql> select * from DemoTable1985;

This will produce the following output −

+----------+
| DueDate  |
+----------+
| Jan 2017 |
| May 2018 |
| Aug 2015 |
+----------+
3 rows in set (0.00 sec)

Following is the query to convert date format −

mysql> select date_format(str_to_date(DueDate,'%b %Y'), '%Y-%m-01') as Result from DemoTable1985;

This will produce the following output −

+------------+
| Result     |
+------------+
| 2017-01-01 |
| 2018-05-01 |
| 2015-08-01 |
+------------+
3 rows in set (0.00 sec)
Updated on: 2019-12-31T08:11:30+05:30

200 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements