What is the MySQL query to retrieve the last day of the previous month?

---

You can use the following syntax to get the last day of the previous month for a given date in MySQL:

SELECT sales_date, LAST_DAY(sales_date - INTERVAL 1 MONTH)
FROM sales;

This particular example creates a new column that contains the last day of the previous month for the corresponding date in the sales_date column of the table named sales.

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

Example: How to Get Last Day of Previous Month in MySQL

Suppose we have the following table named sales that contains information about sales made at various grocery stores on various dates:

-- create table 
CREATE TABLE sales (
  store_ID INT PRIMARY KEY,
  item TEXT NOT NULL,
  sales_date DATE NOT NULL
);

-- insert rows into table
INSERT INTO sales VALUES (0001, 'Oranges', '2024-02-10');
INSERT INTO sales VALUES (0002, 'Apples', '2024-11-25');
INSERT INTO sales VALUES (0003, 'Bananas', '2024-06-30');
INSERT INTO sales VALUES (0004, 'Melons', '2024-01-14');
INSERT INTO sales VALUES (0005, 'Grapes', '2024-05-19');

-- view all rows in table
SELECT * FROM sales;

Output:

+----------+---------+------------+
| store_ID | item    | sales_date |
+----------+---------+------------+
|        1 | Oranges | 2024-02-10 |
|        2 | Apples  | 2024-11-25 |
|        3 | Bananas | 2024-06-30 |
|        4 | Melons  | 2024-01-14 |
|        5 | Grapes  | 2024-05-19 |
+----------+---------+------------+

Suppose that we would like to select the dates from the sales_date column and create a new column that contains the last day of the previous month for each corresponding date in the sales_date column.

We can use the following syntax to do so:

SELECT sales_date, LAST_DAY(sales_date - INTERVAL 1 MONTH)
FROM sales;

Output:

+------------+-----------------------------------------+
| sales_date | LAST_DAY(sales_date - INTERVAL 1 MONTH) |
+------------+-----------------------------------------+
| 2024-02-10 | 2024-01-31                              |
| 2024-11-25 | 2024-10-31                              |
| 2024-06-30 | 2024-05-31                              |
| 2024-01-14 | 2023-12-31                              |
| 2024-05-19 | 2024-04-30                              |
+------------+-----------------------------------------+

Notice that the dates in the new column represent the last day of the previous month for the corresponding date in the sales_date column.

If you’d like, you can also use the AS statement to give a specific name to this new column:

SELECT sales_date, LAST_DAY(sales_date - INTERVAL 1 MONTH) AS last_previous
FROM sales;

+------------+---------------+
| sales_date | last_previous |
+------------+---------------+
| 2024-02-10 | 2024-01-31    |
| 2024-11-25 | 2024-10-31    |
| 2024-06-30 | 2024-05-31    |
| 2024-01-14 | 2023-12-31    |
| 2024-05-19 | 2024-04-30    |
+------------+---------------+

Notice that the new column is now named last_previous, which is much easier to read.

Note: This formula works by subtracting one month from the given date, then by finding the last day of that particular month.

For example, consider the date 2/10/2024.

First, the formula subtracts one month to get 1/10/2024.

Then, it uses the LAST_DAY() function to get the last day of that month, which is 1/31/2024.

Additional Resources

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

MySQL: How to Select Rows where Date is Equal to Today