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

Get the Last Day of the Previous Month in MySQL, Working with month-end dates is a common requirement in SQL. A report might need to compare this month’s sales with the previous month’s closing balance, calculate monthly growth, or determine which records were active at the end of the previous month.

The tricky part is that months don’t all have the same number of days. January has 31 days, February can have 28 or 29, and some months have 30 days.

Fortunately, MySQL provides a simple combination of LAST_DAY() and date intervals that handles this automatically.

The basic pattern is:

LAST_DAY(date-INTERVAL1MONTH)

For example:

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

Result:

2024-01-31

The idea is simple: move the date back one month, then ask MySQL for the last day of that month.

How the Formula Works

Consider this date:

2024-02-10

First, subtract one month:

'2024-02-10'-INTERVAL1MONTH

which gives:

2024-01-10

Then apply LAST_DAY():

LAST_DAY('2024-01-10')

which returns:

2024-01-31

So the complete calculation is:

2024-02-10      ↓Subtract 1 month      ↓2024-01-10      ↓LAST_DAY()      ↓2024-01-31

This approach works without needing to know whether the previous month has 28, 29, 30, or 31 days.

Understanding LAST_DAY() in MySQL

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

For example:

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

returns:

2024-05-31

Another example:

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

returns:

2024-02-29

Because 2024 is a leap year.

This makes LAST_DAY() particularly useful for month-end calculations.

Example: Sales Data

Let’s create a sample sales table:

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

Insert 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');

We can view the table with:

SELECT*FROM sales;

The data looks like this:

+----------+---------+------------+| 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 Last Day of the Previous Month

Now we can calculate the previous month’s final day for every sales date:

SELECT  sales_date,  LAST_DAY(sales_date -INTERVAL1MONTH) AS last_previousFROM sales;

The result is:

+------------+---------------+| 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 MySQL automatically handles the change from one year to another.

For example:

2024-01-14    ↓2023-12-14    ↓2023-12-31

No special logic is needed for December-to-January transitions.

Why This Is Better Than Hard-Coding Days

A common mistake is to assume every month has 30 or 31 days.

For example, you might be tempted to manually construct:

YYYY-MM-30

But that approach breaks for February and months with 31 days.

Consider:

January → 31 daysFebruary → 28 or 29 daysApril → 30 daysMay → 31 days

Using:

LAST_DAY()

lets MySQL determine the correct calendar boundary automatically.

That is especially important when working with historical data spanning multiple years.

Leap Years Are Handled Automatically

Consider a date in March 2024:

SELECT LAST_DAY('2024-03-15'-INTERVAL1MONTH);

The result is:

2024-02-29

Now consider the same month in 2023:

SELECT LAST_DAY('2023-03-15'-INTERVAL1MONTH);

The result is:

2023-02-28

You don’t need to write separate logic to detect leap years.

MySQL handles the calendar calculation for you.

A Useful Month-End Reporting Pattern

This technique becomes particularly useful in reporting.

Suppose your report contains a transaction date and you want to know the previous month’s closing date for every transaction:

SELECT  store_ID,  sales_date,  LAST_DAY(sales_date -INTERVAL1MONTH) AS previous_month_endFROM sales;

You could then use that calculated date when comparing transactions against historical month-end values.

For example, it can help with:

• Monthly sales comparisons
• Financial reporting
• Inventory snapshots
• Account balances
• Customer activity
• Subscription analysis
• Month-over-month growth
• Historical reporting

Finding the Previous Month’s Start and End

Sometimes you need the complete previous month rather than just its final day.

The first day of the previous month can be calculated with:

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

The last day can be calculated with:

LAST_DAY(sales_date -INTERVAL1MONTH)

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;

This gives you a useful date range for the previous month.

Using the Previous Month-End in a WHERE Clause

The calculated date can also be used to filter data.

For example, suppose you want to identify records occurring on or before the previous month’s final day:

SELECT*FROM salesWHERE sales_date <= LAST_DAY(sales_date -INTERVAL1MONTH);

In real-world queries, the date used for comparison would often come from another column or a reporting parameter, but the example demonstrates that LAST_DAY() can be used directly inside filtering conditions.

Previous Month-End for the Current Date

You don’t have to start with a column.

You can calculate the previous month’s final day relative to today:

SELECT LAST_DAY(CURDATE() -INTERVAL1MONTH) AS previous_month_end;

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

For example, if today is in March, the expression returns the final day of February.

If today is in January, it returns the final day of December from the previous year.

Previous Month-End vs Current Month-End

You can easily calculate both boundaries.

Previous month:

LAST_DAY(CURDATE() -INTERVAL1MONTH)

Current month:

LAST_DAY(CURDATE())

So you could write:

SELECT  LAST_DAY(CURDATE() -INTERVAL1MONTH) AS previous_month_end,  LAST_DAY(CURDATE()) AS current_month_end;

This can be useful when building dynamic reporting queries.

What Happens at the Beginning of a Year?

The formula also works across year boundaries.

Suppose the date is:

2024-01-14

Subtracting one month gives:

2023-12-14

Then:

LAST_DAY('2023-12-14')

returns:

2023-12-31

Therefore:

LAST_DAY('2024-01-14'-INTERVAL1MONTH)

returns:

2023-12-31

This is one reason the formula is useful: you don’t need separate logic for January.

An Important Edge Case: Dates Near the End of a Month

When subtracting months, calendar arithmetic can have interesting behavior around month-end dates.

For month-end reporting, a robust approach is often to explicitly calculate the desired month boundary rather than assuming that subtracting a fixed number of days will produce the correct result.

For example, don’t assume:

sales_date -INTERVAL30DAY

means “the same date last month.”

A month can contain 28, 29, 30, or 31 days.

If the requirement is specifically the last day of the previous calendar month, use:

LAST_DAY(sales_date -INTERVAL1MONTH)

The SQL communicates the business requirement much more clearly.

Quick Reference

Here are the most useful expressions:

-- Last day of the current monthLAST_DAY(sales_date)-- Last day of the previous monthLAST_DAY(sales_date -INTERVAL1MONTH)-- Last day of the next monthLAST_DAY(sales_date +INTERVAL1MONTH)-- Last day of the previous month relative to todayLAST_DAY(CURDATE() -INTERVAL1MONTH)

Final Takeaway

Getting the last day of the previous month in MySQL doesn’t require complicated calendar logic.

The key expression is:

LAST_DAY(sales_date -INTERVAL1MONTH)

It works in two steps:

Original date     ↓Subtract one month     ↓Find the last day of that month

For example:

SELECT  sales_date,  LAST_DAY(sales_date -INTERVAL1MONTH) AS last_previousFROM sales;

This approach automatically handles different month lengths, leap years, and year transitions.

For anyone working with monthly reporting, financial data, sales analytics, subscriptions, or time-based SQL analysis, LAST_DAY() combined with INTERVAL is a small but extremely useful MySQL technique.