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
- Truncate a Foreign Key Constrained Table
- Add Days to a Datetime
- Find Value in Column with Comma-Separated Values
- Ideas to Debug High-Load Situations (Coming soon)
- Calculate Number of Days Between Two Dates
- Get the Last Day of the Month
- Get the First Day of the Month
- Select Rows With Date Within a Month
- Get Weekday From Date (Coming soon)
- Fixing Invalid JSON Text in Argument 1 to Function json_extract: "Invalid value." (Coming soon)
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!