如何在MySQL查询中将日期YYYYMMDD转换为YY-MM-DD？
要将MySQL中的日期YYYYMMDD转换为YY-MM-DD，请使用以下语法-
select date_format(str_to_date(yourColumnName, '%Y%m%d'),'%Y-%m-%d') from yourTableName;
让我们首先创建一个表-
mysql> create table DemoTable ( ClientId int NOT NULL AUTO_INCREMENT PRIMARY KEY, ClientProjectDeadline varchar(200) );
使用insert命令在表中插入一些记录。我们以YYYYMMDD格式插入了日期-
mysql> insert into DemoTable(ClientProjectDeadline) values('20121221');
mysql> insert into DemoTable(ClientProjectDeadline) values('20190416');
mysql> insert into DemoTable(ClientProjectDeadline) values('20160624');
mysql> insert into DemoTable(ClientProjectDeadline) values('20170131');以下是使用select语句显示表中所有记录的查询-
mysql> select *from DemoTable;
这将产生以下输出-
+----------+-----------------------+ | ClientId | ClientProjectDeadline | +----------+-----------------------+ | 1 | 20121221 | | 2 | 20190416 | | 3 | 20160624 | | 4 | 20170131 | +----------+-----------------------+ 4 rows in set (0.00 sec)
以下是在MySQL中将日期YYYYMMDD转换为YY-MM-DD的查询-
mysql> select date_format(str_to_date(ClientProjectDeadline, '%Y%m%d'),'%Y-%m-%d') from DemoTable;
这将产生以下输出-
+----------------------------------------------------------------------+ | 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)