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.
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!
