Perform MySQL search between two dates

To perform MySQL search between two dates, use BETWEEN keyword. Let us first create a table −

mysql> create table DemoTable1456
   -> (
   -> CustomerName varchar(30),
   -> StartOfferDate  date,
   -> EndOfferDate date
   -> );
Query OK, 0 rows affected (0.54 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable1456 values('Chris','2019-09-01','2019-09-30');
Query OK, 1 row affected (0.18 sec)
mysql> insert into DemoTable1456 values('David','2019-09-01','2019-10-30');
Query OK, 1 row affected (0.10 sec)
mysql> insert into DemoTable1456 values('Bob','2018-10-01','2018-10-20');
Query OK, 1 row affected (0.15 sec)

Display all records from the table using select statement −

mysql> select * from DemoTable1456;

This will produce the following output −

+--------------+----------------+--------------+
| CustomerName | StartOfferDate | EndOfferDate |
+--------------+----------------+--------------+
| Chris        |    2019-09-01  |   2019-09-30 |
| David        |     2019-09-01 |   2019-10-30 |
| Bob          |    2018-10-01  |   2018-10-20 |
+--------------+----------------+--------------+
3 rows in set (0.00 sec)

Following is the query to perform search between two dates and fetch records −

mysql> select * from DemoTable1456
   -> where date(now())  between StartOfferDate and EndOfferDate;

This will produce the following output −

+--------------+----------------+--------------+
| CustomerName | StartOfferDate | EndOfferDate |
+--------------+----------------+--------------+
| David        | 2019-09-01    |   2019-10-30  |
+--------------+----------------+--------------+
1 row in set (0.04 sec)

AmitDiwan

Updated on 10-Dec-2019 06:23:04

• Related Questions & Answers
• How to search date between two dates in MongoDB?
• How to query between two dates in MySQL?
• Select the date records between two dates in MySQL
• Find the number of logins between two dates in MySQL
• Get the SUM of records between two given dates in MySQL
• MySQL query to select all data between range of two dates?
• Calculate minutes between two dates in C#
• Find objects between two dates in MongoDB?
• MySQL to perform DateTime comparison and find the difference between dates in different columns
• Return all dates between two dates in an array in PHP
• How to check whether now() falls between two specific dates in MySQL?
• How to create a vector with dates between two dates in R?
• Finding Number of Days Between Two Dates JavaScript
• Python Pandas – Filter DataFrame between two dates
• How can I calculate full 24hour days between two specified dates in MySQL?