MySQL中如何转换不规范格式的日期？

假设日期是varchar数据类型，即不是日期格式。

首先让我们创建一个表。以下是查询语句 −

mysql> create table convertDateDemo
   -> (
   -> AdmissionDate varchar(200)
   -> );
Query OK, 0 rows affected (0.63 sec)

以下是使用insert命令向表中插入一些记录的查询语句 −

mysql> insert into convertDateDemo values('April 04 2019');
Query OK, 1 row affected (0.18 sec)

mysql> insert into convertDateDemo values('May 05 2018');
Query OK, 1 row affected (0.16 sec)

mysql> insert into convertDateDemo values('January 01 2012');
Query OK, 1 row affected (0.12 sec)

mysql> insert into convertDateDemo values('June 12 2016');
Query OK, 1 row affected (0.16 sec)

以下是使用select语句显示表中所有记录的查询语句 −

mysql> select * from convertDateDemo;

这将产生以下输出 −

+-----------------+
| AdmissionDate   |
+-----------------+
| April 04 2019   |
| May 05 2018     |
| January 01 2012 |
| June 12 2016    |
+-----------------+
4 rows in set (0.00 sec)

以下是使用MySQL转换日期的查询语句 −

mysql> select STR_TO_DATE(AdmissionDate, '%M %e %Y %l:%i%p') AS ConvertDate from
convertDateDemo;

这将产生以下输出 −

+---------------------+
| ConvertDate         |
+---------------------+
| 2019-04-04 00:00:00 |
| 2018-05-05 00:00:00 |
| 2012-01-01 00:00:00 |
| 2016-06-12 00:00:00 |
+---------------------+
4 rows in set (0.05 sec)