MySQL: How to Return All Rows Between Two Dates


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());

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 |
+----------+--------+---------------------+

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:

MySQL: How to Insert Date
MySQL: How to Insert Datetime
MySQL: How to Truncate Date

Leave a Reply

Your email address will not be published. Required fields are marked *