忽略MySQL查询中带有日期范围的年份？
要忽略带日期范围的年份，请在DATE_FORMAT()和Between子句之间使用。让我们首先创建一个演示表。创建表的查询如下-
mysql> create table igonreYearDemo -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> ShippingDate date -> );
使用insert命令在表中插入一些记录。查询如下-
mysql> insert into igonreYearDemo(ShippingDate) values('2016-01-31');
mysql> insert into igonreYearDemo(ShippingDate) values('2018-01-31');
mysql> insert into igonreYearDemo(ShippingDate) values('2015-06-23');
mysql> insert into igonreYearDemo(ShippingDate) values('2015-06-23');
mysql> insert into igonreYearDemo(ShippingDate) values('2014-02-01');
mysql> insert into igonreYearDemo(ShippingDate) values('2019-01-31');
mysql> insert into igonreYearDemo(ShippingDate) values('2019-02-14');
mysql> insert into igonreYearDemo(ShippingDate) values('2019-03-12');使用select语句显示表中的所有记录。查询如下-
mysql> select *from igonreYearDemo;
这是输出-
+----+--------------+ | Id | ShippingDate | +----+--------------+ | 1 | 2016-01-31 | | 2 | 2018-01-31 | | 3 | 2015-06-23 | | 4 | 2015-06-23 | | 5 | 2014-02-01 | | 6 | 2019-01-31 | | 7 | 2019-02-14 | | 8 | 2019-03-12 | +----+--------------+ 8 rows in set (0.00 sec)
以下是忽略日期范围内的年份的查询-
mysql> select *from igonreYearDemo tbl where DATE_FORMAT(tbl.ShippingDate, '%m-%d') between '01-01' and '03-31';
这是输出-
+----+--------------+ | Id | ShippingDate | +----+--------------+ | 1 | 2016-01-31 | | 2 | 2018-01-31 | | 5 | 2014-02-01 | | 6 | 2019-01-31 | | 7 | 2019-02-14 | | 8 | 2019-03-12 | +----+--------------+ 6 rows in set (0.00 sec)