Table of Contents
To return all rows greater than a specified date in MySQL, you would need to first use the SELECT statement to choose the table and columns you want to retrieve data from. Then, use the WHERE clause to specify the column and condition, in this case, the date column and the greater than operator. Next, use the ORDER BY clause to sort the results in ascending or descending order. Finally, use the LIMIT clause to limit the number of results returned, if desired.
You can use the following basic syntax in MySQL to return all rows in a table where a date column is greater than a specific date:
SELECT *
FROM sales
WHERE sales_date > '2020-01-01'; This particular example selects all rows in the table named sales where the date in the sales_date column is greater than (i.e. more recent than) 1/1/2020.
The following example shows how to use this syntax in practice.
Example: How to Return All Rows Greater Than Date 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 greater than 1/1/2020.
We can use the following syntax to do so:
SELECT *
FROM sales
WHERE sales_date > '2020-01-01'; 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 greater than 1/1/2020.
Note: When querying with dates in MySQL, you must use a YYYY-MM-DD format or you will receive an error.
Additional Resources
MySQL: How to Return All Rows Between Two Dates