How can I get the first day of the previous month using MySQL?

---

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

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

This particular example creates a new column that contains the first 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 First 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 first 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 2 MONTH) + INTERVAL 1 DAY
FROM sales;

Output:

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

Notice that the dates in the new column represent the first 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 2 MONTH) + INTERVAL 1 DAY AS first_previous
FROM sales;

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

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

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

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

First, the formula subtracts two months to get 12/10/2023.

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

Then, it adds one day to get 1/1/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