在 MySQL 中格式化日期以返回月份名称和年份?
mysqlmysqli database
使用 DATE_FORMAT() 并设置说明符以仅显示月份名称和年份。让我们首先创建一个表 −
mysql> create table DemoTable ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, AdmissionDate date ); Query OK, 0 rows affected (0.69 sec)
使用 insert 命令在表中插入记录 −
mysql> insert into DemoTable(AdmissionDate) values('2013-04-21');
Query OK, 1 row affected (0.18 sec)
mysql> insert into DemoTable(AdmissionDate) values('2014-01-31');
Query OK, 1 row affected (0.20 sec)
mysql> insert into DemoTable(AdmissionDate) values('2016-09-01');
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable(AdmissionDate) values('2018-12-12');
Query OK, 1 row affected (0.11 sec)
mysql> insert into DemoTable(AdmissionDate) values('2019-04-01');
Query OK, 1 row affected (0.15 sec)
使用 select 命令显示表中的记录 −
mysql> select *from DemoTable;
这将产生以下输出 −
+----+---------------+ | Id | AdmissionDate | +----+---------------+ | 1 | 2013-04-21 | | 2 | 2014-01-31 | | 3 | 2016-09-01 | | 4 | 2018-12-12 | | 5 | 2019-04-01 | +----+---------------+ 5 rows in set (0.00 sec)
这是在 MySQL 中格式化日期以返回 MonthName 和 Year 的查询 −
mysql> select DATE_FORMAT(AdmissionDate,'%M %Y') AS MonthNameAndYear FROM DemoTable;
这将产生以下输出 −
+------------------+ | MonthNameAndYear | +------------------+ | April 2013 | | January 2014 | | September 2016 | | December 2018 | | April 2019 | +------------------+ 5 rows in set (0.02 sec)
