MySQL query to find the date records wherein the current date and time is in between the JoiningDate and RelievingDate

Use BETWEEN to find the date and time between joining and relieving date. NOW() is used to get the current date and time for comparison.

Let us first create a table −

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

Insert some records in the table using insert command −

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)

Display all records from the table using select statement −

mysql> select *from DemoTable771;

This will produce the following output -

+---------------------+---------------------+
| 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)

Following is the MySQL query to find the date records wherein the current date and time is in between the JoiningDate and RelievingDate −

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

This will produce the following output -

+---------------------+---------------------+
| 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)