如何在 MySQL 中查询两个日期之间的日期?
mysqlmysqli database
您可以借助 BETWEEN 语句查询日期之间的日期。语法如下 −
select *from yourTableName where yourColumnName between ‘yourStartingDate’ and curdate().
使用 curdate() 或 now()，这两个函数都可以使用。为了理解上述语法，让我们创建一个表 −
mysql> create table BetweenDateDemo −> ( −> StartDate datetime −> ); Query OK, 0 rows affected (0.78 sec)
使用以下查询在表中插入一些记录 −
mysql> insert into BetweenDateDemo values(date_add(now(),interval -1 year)); Query OK, 1 row affected (0.11 sec) mysql> insert into BetweenDateDemo values(date_add(now(),interval -2 year)); Query OK, 1 row affected (0.13 sec) mysql> insert into BetweenDateDemo values(date_add(now(),interval -3 year)); Query OK, 1 row affected (0.13 sec) mysql> insert into BetweenDateDemo values(date_add(now(),interval 1 year)); Query OK, 1 row affected (0.12 sec) mysql> insert into BetweenDateDemo values(date_add(now(),interval 2 year)); Query OK, 1 row affected (0.12 sec) mysql> insert into BetweenDateDemo values(date_add(now(),interval 3 year)); Query OK, 1 row affected (0.16 sec)
现在您可以借助 select 语句显示表中的所有记录。查询如下 −
mysql> select *from BetweenDateDemo;
以下是输出 −
+---------------------+ | StartDate | +---------------------+ | 2017-12-08 11:45:47 | | 2016-12-08 11:45:56 | | 2015-12-08 11:46:01 | | 2019-12-08 11:46:05 | | 2020-12-08 11:46:11 | | 2021-12-08 11:46:15 | +---------------------+ 6 rows in set (0.00 sec)
现在使用上面讨论的语法在日期之间执行选择 −
mysql> select *from BetweenDateDemo where StartDate between '2014-8-12' and curdate();
以下是输出 −
+---------------------+ | StartDate | +---------------------+ | 2017-12-08 11:45:47 | | 2016-12-08 11:45:56 | | 2015-12-08 11:46:01 | +---------------------+ 3 rows in set (0.00 sec)
使用 ORDER BY 进行排序。查询如下 −
mysql> select *from BetweenDateDemo where StartDate between '2014-8-12' and curdate() order by StartDate;
以下是输出 −
+---------------------+ | StartDate | +---------------------+ | 2015-12-08 11:46:01 | | 2016-12-08 11:45:56 | | 2017-12-08 11:45:47 | +---------------------+ 3 rows in set (0.00 sec)
