MySQL 从日期格式中提取年份?
mysqlmysqli database
要从日期格式中提取年份，您可以使用 MySQL 内置函数 YEAR()。查询如下 −
mysql> SELECT YEAR(curdate()) as OnlyYearFromCurrentDate;
以下是输出 −
+-------------------------+ | OnlyYearFromCurrentDate | +-------------------------+ | 2018 | +-------------------------+ 1 row in set (0.00 sec)
或者你可以按以下方式进行 −
mysql> SELECT YEAR(date(now())) as OnlyYearFromCurrentDate;
以下是输出 −
+-------------------------+ | OnlyYearFromCurrentDate | +-------------------------+ | 2018 | +-------------------------+ 1 row in set (0.00 sec)
这样，即使列为 varchar，您也可以实现这一点。让我们首先创建一个表 −
mysql> create table ExtractYearDemo −> ( −> YourDueTime varchar(200) −> ); Query OK, 0 rows affected (1.15 sec)
使用 insert 命令向表中插入记录。查询如下 −
mysql> insert into ExtractYearDemo values('27-10-2011');
Query OK, 1 row affected (0.16 sec)
mysql> insert into ExtractYearDemo values('28-11-2012');
Query OK, 1 row affected (0.15 sec)
mysql> insert into ExtractYearDemo values('29-12-2018');
Query OK, 1 row affected (0.14 sec)
mysql> insert into ExtractYearDemo values('01-01-2019');
Query OK, 1 row affected (0.15 sec)
使用 SELECT 语句显示表中的所有记录。查询如下 −
mysql> select *from ExtractYearDemo;
以下是输出 −
+-------------+ | YourDueTime | +-------------+ | 27-10-2011 | | 28-11-2012 | | 29-12-2018 | | 01-01-2019 | +-------------+ 4 rows in set (0.00 sec)
以下是当表列为 varchar 类型时从表列中提取年份的查询 −
mysql> select YEAR(STR_TO_DATE(yourDueTime, "%d-%m-%Y")) As OnlyYearFromDate from ExtractYearDemo;
以下是显示年份的输出 −
+------------------+ | OnlyYearFromDate | +------------------+ | 2011 | | 2012 | | 2018 | | 2019 | +------------------+ 4 rows in set (0.00 sec)
