MySQL 查询以查找当前日期和时间介于 JoiningDate 和 RelievingDate 之间的日期记录

使用 BETWEEN 查找 JoiningDate 和 RelievingDate 之间的日期和时间。NOW() 用于获取当前日期和时间进行比较。

我们先创建一个表 -

mysql> create table DemoTable771 (
   Joiningdate datetime,
   Relievingdate datetime
);
Query OK, 0 rows affected (1.15 sec)

使用 insert 命令向表中插入一些记录 -

mysql> insert into DemoTable771 values('2016-01-21','2016-09-23');
Query OK, 1 row affected (0.27 sec)
mysql> insert into DemoTable771 values('2019-01-21','2019-09-23');
Query OK, 1 row affected (0.16 sec)
mysql> insert into DemoTable771 values('2017-04-01','2018-12-31');
Query OK, 1 row affected (0.20 sec)
mysql> insert into DemoTable771 values('2019-04-01','2019-12-31');
Query OK, 1 row affected (0.25 sec)

使用 select 语句显示表中的所有记录 -

mysql> select *from DemoTable771;

这将产生以下输出 -

+---------------------+---------------------+
| Joiningdate         | Relievingdate       |
+---------------------+---------------------+
| 2016-01-21 00:00:00 | 2016-09-23 00:00:00 |
| 2019-01-21 00:00:00 | 2019-09-23 00:00:00 |
| 2017-04-01 00:00:00 | 2018-12-31 00:00:00 |
| 2019-04-01 00:00:00 | 2019-12-31 00:00:00 |
+---------------------+---------------------+
4 rows in set (0.00 sec)

以下是在当前日期和时间介于 JoiningDate 和 RelievingDate 之间的日期记录的 MySQL 查询 -

mysql> select *from DemoTable771 where NOW() between Joiningdate and Relievingdate;

这将产生以下输出 -

+---------------------+---------------------+
| Joiningdate         | Relievingdate       |
+---------------------+---------------------+
| 2019-01-21 00:00:00 | 2019-09-23 00:00:00 |
| 2019-04-01 00:00:00 | 2019-12-31 00:00:00 |
+---------------------+---------------------+
2 rows in set (0.07 sec)

AmitDiwan

更新时间：2019-09-03

80 次浏览

开启你的 事业

完成课程，获得认证

开始