MySQL comes with dozens of date and time functions. It’s great to have these functions at hand in your SQL queries. The database can already do the calculations you would do afterward in your code. One of these date functions is to get the last day of a month based on a given date.

This tutorial shows you how to retrieve the date of a month’s last day.

MySQL Series Overview

Use the MySQL LAST_DAY Function

One of MySQL’s date and time functions is the LAST_DAY function. It returns the date for the last day of the month based on a given date or date time value. The LAST_DAY function returns NULL if you’re providing an invalid value or NULL as an argument:

SELECT LAST_DAY('2024-10-03');  
-- '2024-10-31'

SELECT LAST_DAY('2025-01-01 09:00:00');  
-- '2025-01-31'

SELECT LAST_DAY(NOW());  
-- 2024-06-30
-- ^ this assumes "NOW()" is in June

SELECT LAST_DAY(NULL);  
-- NULL

SELECT LAST_DAY('2024-08-55');  
-- NULL

You can use the LAST_DAY function in other sections of your SQL statement. For example, you may use it in the WHERE part to filter the data set. Here’s a sample query filtering all users created before or on November 30th, 2024:

SELECT *  
FROM users  
WHERE createdon <= LAST_DAY('2024-11-30')  

That’s it!

Mentioned Resources

• MySQL docs for date and time functions