How to convert date YYYYMMDD to YY-MM-DD in MySQL query?

To convert date YYYYMMDD to YY-MM-DD in MySQL, use the below syntax −

select date_format(str_to_date(yourColumnName, '%Y%m%d'),'%Y-%m-%d') from yourTableName;

Let us first create a table −

mysql> create table DemoTable
(
   ClientId int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   ClientProjectDeadline varchar(200)
);
Query OK, 0 rows affected (0.57 sec)

Insert some records in the table using insert command. We have inserted dates in the YYYYMMDD format −

mysql> insert into DemoTable(ClientProjectDeadline) values('20121221');
Query OK, 1 row affected (0.19 sec)
mysql> insert into DemoTable(ClientProjectDeadline) values('20190416');
Query OK, 1 row affected (0.53 sec)
mysql> insert into DemoTable(ClientProjectDeadline) values('20160624');
Query OK, 1 row affected (0.20 sec)
mysql> insert into DemoTable(ClientProjectDeadline) values('20170131');
Query OK, 1 row affected (0.20 sec)

Following is the query to display all records from the table using select statement −

mysql> select *from DemoTable;

This will produce the following output −

+----------+-----------------------+
| ClientId | ClientProjectDeadline |
+----------+-----------------------+
| 1        | 20121221              |
| 2        | 20190416              |
| 3        | 20160624              |
| 4        | 20170131              |
+----------+-----------------------+
4 rows in set (0.00 sec)

Following is the query to convert date YYYYMMDD to YY-MM-DD in MySQL −

mysql> select date_format(str_to_date(ClientProjectDeadline, '%Y%m%d'),'%Y-%m-%d') from DemoTable;

This will produce the following output −

+----------------------------------------------------------------------+
| date_format(str_to_date(ClientProjectDeadline, '%Y%m%d'),'%Y-%m-%d') |
+----------------------------------------------------------------------+
| 2012-12-21                                                           |
| 2019-04-16                                                           |
| 2016-06-24                                                           |
| 2017-01-31                                                           |
+----------------------------------------------------------------------+
4 rows in set (0.05 sec)

Ankith Reddy

Updated on 30-Jul-2019 22:30:25

• Related Questions & Answers
• How to convert MM/YY to YYYY-MM-DD in MYSQL?
• Convert MM/DD/YY to Unix timestamp in MySQL?
• MySQL query to convert YYYY-MM-DD to DD Month, YYYY date format
• How to convert MM/YY to YYYY-MM-DD with a specific day in MySQL?
• Converting date from 'dd/mm/yyyy' to 'yyyymmdd' in MySQL
• MySQL date format to convert dd.mm.yy to YYYY-MM-DD?
• MySQL date format DD/MM/YYYY select query?
• Format date with SimpleDateFormat('MM/dd/yy') in Java
• Convert MySQL date format from yyyy-mm-ddThh:mm:ss.sssZ to yyyy-mm-dd hh:mm:ss ?
• Get date format DD/MM/YYYY with MySQL Select Query?
• How to convert Python date string mm/dd/yyyy to datetime?
• MySQL - Convert YYYY-MM-DD to UNIX timestamp
• Converting date type SYDATUM in a format like MM/DD/YY in SAP ABAP
• Convert dd/mm/yyyy string to Unix timestamp in MySQL?
• How to parse Date from String in the format: dd/MM/yyyy to dd/MM/yyyy in java?