SELECT MySQL rows where today's date is between two DATE columns?

To select MySQL rows where today’s date is between two date columns, you need to use AND operator. The syntax is as follows:

SELECT *FROM yourTableName WHERE yourDateColumnName1 <=’yourDateValue’ AND
yourDateColumnName2 >= ‘’yourDateValue’;

To understand the above syntax, let us create a table. The query to create a table is as follows:

mysql> create table selectDates
   -> (
   -> Id int NOT NULL AUTO_INCREMENT,
   -> StartingDate date,
   -> EndingDate date,
   -> PRIMARY KEY(Id)
   -> );
Query OK, 0 rows affected (0.80 sec)

Now you can insert some records in the table using insert command. The query is as follows:

mysql> insert into selectDates(StartingDate,EndingDate) values('2019-01-11','2019-01-23');
Query OK, 1 row affected (0.17 sec)
mysql> insert into selectDates(StartingDate,EndingDate) values('2019-01-10','2019-01-23');
Query OK, 1 row affected (0.17 sec)
mysql> insert into selectDates(StartingDate,EndingDate) values('2019-01-30','2019-01-30');
Query OK, 1 row affected (0.12 sec)
mysql> insert into selectDates(StartingDate,EndingDate) values('2019-10-14','2019-10-28');
Query OK, 1 row affected (0.17 sec)
mysql> insert into selectDates(StartingDate,EndingDate) values('2019-10-14','2019-10-20');
Query OK, 1 row affected (0.19 sec)
mysql> insert into selectDates(StartingDate,EndingDate) values('2019-11-17','2019-11-19');
Query OK, 1 row affected (0.52 sec)
mysql> insert into selectDates(StartingDate,EndingDate) values('2019-12-21','2019-12-31');
Query OK, 1 row affected (0.19 sec)
mysql> insert into selectDates(StartingDate,EndingDate) values('2019-01-06','2019-01-21');
Query OK, 1 row affected (0.16 sec)
mysql> insert into selectDates(StartingDate,EndingDate) values('2019-01-07','2019-01-17');
Query OK, 1 row affected (0.18 sec)

Display all records from the table using select statement. The query is as follows:

mysql> select *from selectDates;

The following is the output:

+----+--------------+------------+
| Id | StartingDate | EndingDate |
+----+--------------+------------+
|  1 | 2019-01-11   | 2019-01-23 |
|  2 | 2019-01-10   | 2019-01-23 |
|  3 | 2019-01-30   | 2019-01-30 |
|  4 | 2019-10-14   | 2019-10-28 |
|  5 | 2019-10-14   | 2019-10-20 |
|  6 | 2019-11-17   | 2019-11-19 |
|  7 | 2019-12-21   | 2019-12-31 |
|  8 | 2019-01-06   | 2019-01-21 |
|  9 | 2019-01-07   | 2019-01-17 |
+----+--------------+------------+
9 rows in set (0.00 sec)

Here is the query to select today’s date between two date columns:

select *from selectDates where StartingDate <='2019-01-10' AND EndingDate >='2019-01-10';

The following is the output:

+----+--------------+------------+
| Id | StartingDate | EndingDate |
+----+--------------+------------+
|  2 | 2019-01-10   | 2019-01-23 |
|  8 | 2019-01-06   | 2019-01-21 |
|  9 | 2019-01-07   | 2019-01-17 |
+----+--------------+------------+
3 rows in set (0.00 sec)

Arjun Thakur

Published on 26-Feb-2019 08:25:06

• Related Questions & Answers
• MySQL query to select date from 00:00 to today’s date
• How to subtract date from today's date in JavaScript?
• How to get today's date in Java8?
• How to select yesterday's date in MySQL?
• MySQL query to select closest date from today?
• Get today's date in (YYYY-MM-DD) format in MySQL?
• MySQL Select Date Equal to Today and return results for the same date?
• Select the date records between two dates in MySQL
• MySQL Select Rows where two columns do not have the same value?
• How to return today's records using DATE from DATETIME Field?
• Get the Day of the Week from Today's Date in Java
• Display today’s date in Java with SimpleDateFormat
• How to get the date between TODAY and TODAY-7”?
• Display record with today and tomorrow’s date from a column with date record in MySQL
• Select all rows except from today in MySQL?