How can MySQL return all rows that are less than a certain date?

---

You can use the following basic syntax in MySQL to return all rows in a table where a date column is less 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 less than (i.e. earlier than) 1/1/2020.

The following example shows how to use this syntax in practice.

Example: How to Return All Rows Less 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 less 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          |
+----------+---------+---------------------+
|        1 | Oranges | 2015-01-12 03:45:00 |
|        3 | Bananas | 2009-06-30 09:01:39 |
+----------+---------+---------------------+

Notice that each of the rows in the resulting table have a date in the sales_date column that is less than 1/1/2020.

If you would like the dates to be shown in order from latest to most recent, you can use an ORDER BY statement as well:

SELECT *
FROM sales
WHERE sales_date < '2020-01-01'
ORDER BY sales_date;

+----------+---------+---------------------+
| store_ID | item    | sales_date          |
+----------+---------+---------------------+
|        3 | Bananas | 2009-06-30 09:01:39 |
|        1 | Oranges | 2015-01-12 03:45:00 |
+----------+---------+---------------------+

Note: When querying with dates in MySQL, you must use a YYYY-MM-DD format or you will receive an error.

Additional Resources

The following tutorials explain how to perform other common tasks in MySQL:

MySQL: How to Return All Rows Between Two Dates