How to parse dates in MySQL?

Use the STR_TO_DATE() function to parse dates in MySQL. The syntax is as follows -

select str_to_date(yourColumName,’format’) as anyVariableName from yourTableName;

The format in the above syntax is "%d-%b-%y".

Now to understand the above function, let us create a table. Following is the query to create the table -

mysql> create table ParseDateDemo
   −> (
   −> yourDate varchar(200)
   −> );
Query OK, 0 rows affected (0.55 sec)

Insert some records into the table with the help of select statement. The query is as follows -

mysql> insert into ParseDateDemo values('10-Nov-18');
Query OK, 1 row affected (0.11 sec)

mysql> insert into ParseDateDemo values('15-Nov-18');
Query OK, 1 row affected (0.17 sec)

mysql> insert into ParseDateDemo values('10-Dec-18');
Query OK, 1 row affected (0.14 sec)

mysql> insert into ParseDateDemo values('10-Aug-17');
Query OK, 1 row affected (0.14 sec)

Use the select statement to display the records in the table. The query is as follows -

mysql> select *from ParseDateDemo;

The following is the output -

+-----------+
| yourDate  |
+-----------+
| 10-Nov-18 |
| 15-Nov-18 |
| 10-Dec-18 |
| 10-Aug-17 |
+-----------+
4 rows in set (0.00 sec)

The following is the query to parse date in MySQL -

mysql> select str_to_date(yourDate,'%d-%b-%y') as DateNumber from ParseDateDemo;

This is the output -

+------------+
| DateNumber |
+------------+
| 2018-11-10 |
| 2018-11-15 |
| 2018-12-10 |
| 2017-08-10 |
+------------+
4 rows in set (0.02 sec)

The above is the detailed content of How to parse dates in MySQL?. For more information, please follow other related articles on the PHP Chinese website!