How to Get the First Day of the Previous Month in MySQL

Get the First Day of the Previous Month in MySQL,Working with dates in SQL often looks simple until you need to calculate calendar boundaries. Finding the first day of the previous month is a common example.

You might need it when creating monthly reports, comparing sales periods, calculating month-over-month changes, or filtering transactions from a previous reporting period.

Fortunately, MySQL can calculate this dynamically without manually figuring out how many days are in each month.

The following expression returns the first day of the previous month for a given date:

LAST_DAY(sales_date -INTERVAL2MONTH) +INTERVAL1DAY

For example:

SELECT  sales_date,  LAST_DAY(sales_date -INTERVAL2MONTH) +INTERVAL1DAYAS first_previousFROM sales;

Let’s see why this works and how you can use it in practical SQL queries.

The Basic Idea

The formula uses three simple operations:

Original date     ↓Subtract 2 months     ↓Find the last day of that month     ↓Add 1 day     ↓First day of the previous month

Consider:

2024-02-10

Subtract two months:

2023-12-10

Find the last day of December:

2023-12-31

Add one day:

2024-01-01

Therefore:

2024-02-10 → 2024-01-01

Understanding LAST_DAY()

The LAST_DAY() function returns the final calendar day of the month containing a given date.

For example:

SELECT LAST_DAY('2024-05-19');

returns:

2024-05-31

For February:

SELECT LAST_DAY('2024-02-10');

returns:

2024-02-29

MySQL automatically handles leap years and different month lengths.

That is what makes LAST_DAY() useful for calendar calculations.

Creating a Sample Sales Table

Suppose we have a table called sales:

CREATETABLE sales (  store_ID INTPRIMARYKEY,  item TEXT NOTNULL,  sales_date DATENOTNULL);

We can add some sample records:

INSERTINTO sales VALUES (1, 'Oranges', '2024-02-10');INSERTINTO sales VALUES (2, 'Apples', '2024-11-25');INSERTINTO sales VALUES (3, 'Bananas', '2024-06-30');INSERTINTO sales VALUES (4, 'Melons', '2024-01-14');INSERTINTO sales VALUES (5, 'Grapes', '2024-05-19');

View the data:

SELECT*FROM sales;

The table contains:

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

Finding the First Day of the Previous Month

Now apply the formula:

SELECT  sales_date,  LAST_DAY(sales_date -INTERVAL2MONTH) +INTERVAL1DAYAS first_previousFROM sales;

The result is:

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

The AS first_previous alias makes the resulting column easier to understand.

Breaking Down a Real Example

Let’s take:

2024-11-25

The calculation is:

LAST_DAY('2024-11-25'-INTERVAL2MONTH) +INTERVAL1DAY

Step 1: Subtract two months

2024-11-25     ↓2024-09-25

Step 2: Find the last day

LAST_DAY('2024-09-25')

returns:

2024-09-30

Step 3: Add one day

2024-09-30 + 1 day

produces:

2024-10-01

And October 1 is exactly the first day of the previous month relative to November 25.

Why Subtract Two Months?

At first glance, subtracting two months might seem strange because we’re trying to find the previous month.

The trick is that LAST_DAY() gives us the end of the month we supply.

We therefore go back two months, find the end of that month, and move forward one day.

For a date in February:

February 10    ↓December 10    ↓December 31    ↓January 1

The final result is the beginning of the previous month.

A More Intuitive Alternative

There is another approach that many developers find easier to read:

DATE_FORMAT(sales_date -INTERVAL1MONTH, '%Y-%m-01')

For example:

SELECT  sales_date,  DATE_FORMAT(sales_date -INTERVAL1MONTH, '%Y-%m-01') AS first_previousFROM sales;

This directly says:

Take the previous month and format its date as the first day of that month.

Both approaches can be useful.

The LAST_DAY() method is particularly helpful when you’re already working with month-end calculations.

Comparing the Two Approaches

For a simple query, the DATE_FORMAT() approach can be easier to understand. The LAST_DAY() approach is useful when you’re working with both the beginning and end of reporting periods.

Finding Both Previous Month Boundaries

A common reporting requirement is to find both the first and last day of the previous month.

You can calculate them together:

SELECT  sales_date,  DATE_FORMAT(    sales_date -INTERVAL1MONTH,'%Y-%m-01'  ) AS previous_month_start,  LAST_DAY(    sales_date -INTERVAL1MONTH  ) AS previous_month_endFROM sales;

For example, a date in May 2024 would produce:

previous_month_start → 2024-04-01previous_month_end   → 2024-04-30

This gives you a complete monthly reporting window.

Using It for Monthly Sales Analysis

Suppose you want to compare current sales with the previous month’s activity.

Knowing the previous month’s start and end dates makes it easier to define the reporting period.

For example:

Previous Month2024-04-01     ↓2024-04-30

You can then use these boundaries when filtering transactions, joining tables, or calculating monthly metrics.

This is useful for:

• Sales reports
• Revenue analysis
• Customer activity
• Inventory reports
• Subscription analytics
• Financial dashboards
• Month-over-month comparisons
• Automated business reports

Handling January Automatically

One of the biggest advantages of using date functions instead of manually constructing dates is that year transitions are handled automatically.

Consider:

2024-01-14

The previous month is December 2023.

Using:

LAST_DAY('2024-01-14'-INTERVAL2MONTH) +INTERVAL1DAY

MySQL calculates:

2024-01-14     ↓2023-11-14     ↓2023-11-30     ↓2023-12-01

The result is:

2023-12-01

No special January condition is necessary.

Leap Years Are Not a Problem

Calendar calculations become especially useful around February.

For example, 2024 is a leap year.

If you calculate boundaries around February, MySQL automatically knows that February contains 29 days.

You don’t need to write logic such as:

IF leap year THEN 29ELSE 28

The date functions handle the calendar rules.

First Day of the Previous Month Relative to Today

You can also calculate the first day of the previous month without using a table column.

Use CURDATE():

SELECT  LAST_DAY(CURDATE() -INTERVAL2MONTH) +INTERVAL1DAYAS previous_month_start;

This is useful for automated reports because the result changes automatically as the current date changes.

An alternative is:

SELECT  DATE_FORMAT(    CURDATE() -INTERVAL1MONTH,'%Y-%m-01'  ) AS previous_month_start;

A Useful Set of MySQL Date Expressions

Once you understand these functions, several common calendar calculations become easy.

First day of the current month

DATE_FORMAT(sales_date, '%Y-%m-01')

Last day of the current month

LAST_DAY(sales_date)

First day of the previous month

DATE_FORMAT(sales_date -INTERVAL1MONTH, '%Y-%m-01')

Last day of the previous month

LAST_DAY(sales_date -INTERVAL1MONTH)

These four expressions cover many everyday month-based reporting requirements.

A Practical Example for Reporting

Suppose you have a large transaction table and need to identify the previous month’s reporting period.

You could create both boundaries:

SELECT  sales_date,  DATE_FORMAT(    sales_date -INTERVAL1MONTH,'%Y-%m-01'  ) AS previous_month_start,  LAST_DAY(    sales_date -INTERVAL1MONTH  ) AS previous_month_endFROM sales;

The resulting columns provide a clean reporting window:

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

Final Takeaway

Finding the first day of the previous month is a small SQL problem with plenty of real-world applications.

The LAST_DAY() approach can be written as:

LAST_DAY(sales_date -INTERVAL2MONTH) +INTERVAL1DAY

The calculation works by:

1. Moving the date back two months.
2. Finding the final day of that month.
3. Adding one day.

For example:

2024-02-10    ↓2023-12-10    ↓2023-12-31    ↓2024-01-01

An alternative, often simpler expression is:

DATE_FORMAT(sales_date -INTERVAL1MONTH, '%Y-%m-01')

Both techniques can be valuable when working with MySQL date calculations, monthly reporting, sales analytics, financial data, and time-based SQL queries.