Table of Contents
MySQL has a built-in function called BETWEEN that allows you to specify a range of values, such as two dates, and returns all rows that fall within that range. In order for this to work, the dates must be formatted in a way that MySQL recognizes, such as YYYY-MM-DD. The BETWEEN function compares each row’s date value to the specified range and returns all rows that meet the criteria. This makes it a quick and efficient way to retrieve all rows between two dates without having to manually filter through each individual row.
You can use the following basic syntax in MySQL to return all rows in a table where a date column is between two specific dates:
SELECT *
FROM sales
WHERE (sales_date BETWEEN '2020-01-01' AND '2024-01-20'); This particular example selects all rows in the table named sales where the date in the sales_date column is between 1/1/2020 and 1/20/2024.
The following example shows how to use this syntax in practice.
Example: How to Return All Rows Between Two Dates in MySQL
Suppose we have the following table named sales that contains information about sales made at various grocery stores at various times:
-- create table
CREATE TABLE sales (
store_ID INT PRIMARY KEY,
item TEXT NOT NULL,
sales_date DATETIME NOT NULL
);
-- insert rows into table
INSERT INTO sales VALUES (0001, 'Oranges', '2015-01-12 03:45:00');
INSERT INTO sales VALUES (0002, 'Apples', '2020-11-25 15:25:01');
INSERT INTO sales VALUES (0003, 'Bananas', '2009-06-30 09:01:39');
INSERT INTO sales VALUES (0004, 'Melons', '2022-04-09 03:29:55');
INSERT INTO sales VALUES (0005, 'Grapes', '2023-05-19 23:10:04');
-- view all rows in table
SELECT * FROM sales;
Output:
+----------+---------+---------------------+
| store_ID | item | sales_date |
+----------+---------+---------------------+
| 1 | Oranges | 2015-01-12 03:45:00 |
| 2 | Apples | 2020-11-25 15:25:01 |
| 3 | Bananas | 2009-06-30 09:01:39 |
| 4 | Melons | 2022-04-09 03:29:55 |
| 5 | Grapes | 2023-05-19 23:10:04 |
+----------+---------+---------------------+ Suppose that we would like to select all rows where the date in the sales_date column is between 1/1/2020 and 1/20/2024.
We can use the following syntax to do so:
SELECT *
FROM sales
WHERE (sales_date BETWEEN '2020-01-01' AND '2024-01-20'); Output:
+----------+--------+---------------------+
| store_ID | item | sales_date |
+----------+--------+---------------------+
| 2 | Apples | 2020-11-25 15:25:01 |
| 4 | Melons | 2022-04-09 03:29:55 |
| 5 | Grapes | 2023-05-19 23:10:04 |
+----------+--------+---------------------+
Notice that each of the rows in the resulting table have a date in the sales_date column that is between 1/1/2020 and 1/20/2024.
If you would like to return all rows where the date is between a specific starting date and the current date, then you can use the CURDATE() function as follows:
SELECT *
FROM sales
WHERE (sales_date BETWEEN '2020-01-01' AND CURDATE()); +----------+--------+---------------------+
| store_ID | item | sales_date |
+----------+--------+---------------------+
| 2 | Apples | 2020-11-25 15:25:01 |
| 4 | Melons | 2022-04-09 03:29:55 |
| 5 | Grapes | 2023-05-19 23:10:04 |
+----------+--------+---------------------+ Note: This article is being written on 2/12/2024, so this particular query returns all rows between 1/1/2020 and 2/12/2024.
Additional Resources
The following tutorials explain how to perform other common tasks in MySQL: