How to Get Records Between 2 Dates in MySQL

Many times you need to get data between 2 dates for reporting and analysis. It is quite easy to get records between 2 dates in MySQL even though there is no built-in function for it. Here’s the SQL to select records between 2 dates in MySQL, PostgreSQL & SQL Server.

Table of Contents

How to Get Records Between 2 Dates in MySQL

Here’s the SQL to get records between 2 dates in MySQL.

Let’s say you have the following table sales(order_date, sale)

mysql> create table sales(order_date date,sale int);

mysql> insert into sales(order_date,sale)
     values('2020-06-01',250),
     ('2020-06-0',350),
     ('2020-06-02',400),
     ('2020-06-03',250),
     ('2020-06-04',200),
     ('2020-06-05',180),
     ('2020-06-06',150),
     ('2020-06-07',650),
     ('2020-06-08',500),
     ('2020-06-09',150);

mysql> select * from sales;
+------------+------+
| order_date | sale |
+------------+------+
| 2020-06-01 |  250 |
| 2020-06-00 |  350 |
| 2020-06-02 |  400 |
| 2020-06-03 |  250 |
| 2020-06-04 |  200 |
| 2020-06-05 |  180 |
| 2020-06-06 |  150 |
| 2020-06-07 |  650 |
| 2020-06-08 |  500 |
| 2020-06-09 |  150 |
+------------+------+

Bonus Read : How to Get Records from Last 24 Hours

How to Get Rows between 2 dates in MySQL

Here’s the SQL query to get records between 2 dates in MySQL. There are 2 ways to get rows between 2 dates – using mathematical operator, and using BETWEEN function. We will look at both these methods.

Bonus Read : How to Get Last 15 Days Records in MySQL

Using mathematical operators

Let’s say you want to get records where order_date is between 2 dates 2020-06-02 and 2020-06-06. Here’s the SQL to get data between 2 dates using mathematical operators.

mysql> select * from sales
     where order_date>='2020-06-02'
     and order_date<='2020-06-06';
+------------+------+
| order_date | sale |
+------------+------+
| 2020-06-02 |  400 |
| 2020-06-03 |  250 |
| 2020-06-04 |  200 |
| 2020-06-05 |  180 |
| 2020-06-06 |  150 |
+------------+------+

In the above query, we select records where order_date is on or after 2020-06-02 and on or before 2020-06-06. You can also use other mathematical operators such as >,<,= and <> with dates. You can also this SQL query to select records between 2 dates in PostgreSQL, SQL Server and Oracle.

Bonus Read : How to Get First Record in Each Group in MySQL

If you want to select records from a specific date till current date, then you can system functions like current_date or now() in your query.

mysql> select * from sales
     where order_date>'2020-06-02'
     and order_date<current_date;
+------------+------+
| order_date | sale |
+------------+------+
| 2020-06-03 |  250 |
| 2020-06-04 |  200 |
| 2020-06-05 |  180 |
| 2020-06-06 |  150 |
| 2020-06-07 |  650 |
| 2020-06-08 |  500 |
+------------+------+

Using BETWEEN operator

You can also select data between 2 dates using BETWEEN operator

mysql> select * from sales
     where order_date
     BETWEEN '2020-06-02' and '2020-06-06';
+------------+------+
| order_date | sale |
+------------+------+
| 2020-06-02 |  400 |
| 2020-06-03 |  250 |
| 2020-06-04 |  200 |
| 2020-06-05 |  180 |
| 2020-06-06 |  150 |
+------------+------+

While selecting records, BETWEEN operator will include records between those 2 dates you mention, as well as the records on the 2 dates.

Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!

How to Calculate Average Sales Per Week in MySQL

How to Use MySQL Rollup

MySQL DROP FOREIGN KEY Constraint