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)

AmitDiwan

Updated on 03-Sep-2019 08:49:22

• Related Questions & Answers
• MySQL query to get the current date records wherein one of the columns displays current date
• Find the difference between current date and the date records from a MySQL table
• How to get the difference between date records and the current date in MySQL?
• MySQL DATE function to return the difference between current date and joining date
• Select dates between current date and 3 months from the current date in MySQL?
• MySQL query to order and display difference between dates from the current date
• MySQL query to select records with a particular date and time?
• Comparison of varchar date records from the current date in MySQL
• Fetch date records comparing with the current date’s day and month in MySQL
• MySQL time period query to fetch date records from interval of 14 weeks from current date?
• MySQL query to fetch date records greater than the current date after adding days with INTERVAL?
• How to get the current date and time in Java?
• MySQL query to get current datetime and only current date
• Set current date and time to timestamp in MySQL
• Get the number of days between current date and date field?