MySQL从日期格式中提取年份？
要从日期格式中提取年份，可以使用MySQL的内置函数YEAR()。查询如下-
SELECT YEAR(curdate()) as OnlyYearFromCurrentDate;
以下是输出-
+-------------------------+ | OnlyYearFromCurrentDate | +-------------------------+ | 2018 | +-------------------------+ 1 row in set (0.00 sec)
或者您可以按照以下方式做-
SELECT YEAR(date(now())) as OnlyYearFromCurrentDate;
以下是输出-
+-------------------------+ | OnlyYearFromCurrentDate | +-------------------------+ | 2018 | +-------------------------+ 1 row in set (0.00 sec)
这样，当column为varchar时，您甚至可以实现此目的。让我们首先创建一个表-
create table ExtractYearDemo −> ( −> YourDueTime varchar(200) −> );
借助insert命令将记录插入表中。查询如下-
insert into ExtractYearDemo values('27-10-2011');
insert into ExtractYearDemo values('28-11-2012');
insert into ExtractYearDemo values('29-12-2018');
insert into ExtractYearDemo values('01-01-2019');在SELECT语句的帮助下显示表中的所有记录。查询如下-
select *from ExtractYearDemo;
以下是输出-
+-------------+ | YourDueTime | +-------------+ | 27-10-2011 | | 28-11-2012 | | 29-12-2018 | | 01-01-2019 | +-------------+ 4 rows in set (0.00 sec)
以下是当列为varchar类型时要从表列中提取年份的查询-
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)