MySQL | LAST_DAY() Function

The MySQL LAST_DAY() function returns the last day of the month for a given date or datetime. This function takes a date value as an argument and returns the last day of the month for that date. The date argument should be a valid date or datetime.

Syntax

LAST_DAY( Date );

Parameters:

• Date: The LAST_DAY() function takes a single argument Date. It is the date or datetime value for which you want to extract the last day of the month.

Return Value:

• The LAST_DAY() function returns the last day of the month for a valid Date argument.
  • Note: If the argument Date is invalid or null, then the function will also return NULL.

MySQL LAST_DAY() Function Examples

Below are some common examples or usages of the LAST_DAY() function:

Example 1: Extracting last day from a given date

To know the last date of the month December 2017,the LAST_DAY() function can be executed in the following way.

Query:

SELECT LAST_DAY ('2024-12-25')

Output:

'2024-12-31'

Example 2: Extracting the last day from a given datetime

To know the last date of the month December using datetime format, the LAST_DAY() function can be executed in the following way.

Query:

SELECT LAST_DAY ('2024-12-25 08:21:05');

Output:

'2024-12-31'

Example 3: Checking whether it is a leap year or not

To know whether the year is a leap year or not, we can use the LAST_DAY() function to check the last day of the month February of that year. If it is the 29th day then that year is leap otherwise not.

Query:

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

Output:

'2024-02-29'

Example 4: Extracting the last day for the current month

To know the last date of the current month, the LAST_DAY() function is combined with the NOW() or CURDATE() function and can be executed in the following way:

1. Using the NOW() function: The NOW() function in MySQL returns the current date-time stamp.

Query:

SELECT LAST_DAY(NOW());

Output:

'2024-07-31'

2. Using the CURDATE() function: The CURDATE() function in MySQL return the current date in Date format.

Query:

SELECT LAST_DAY(CURDATE());

Output :

'2024-07-31'

Example 5: Extracting the last day of the next month

To know the last date of the next month, the LAST_DAY() function can be executed in the following way:

Query:

SELECT LAST_DAY(CURDATE() + INTERVAL 1 MONTH);

Output:

'2024-07-31'

Important Points About LAST_DAY() Function in MySQL

1. The LAST_DAY() function returns the last day of the month for a specified date. It is useful for date manipulation tasks where month-end dates are required.

2. You can combine LAST_DAY() with other date functions like CURDATE(), DATE_ADD(), and DATE_SUB() to perform more complex date calculations.

3. If an invalid date is provided, LAST_DAY() returns NULL.

4. It is commonly used in queries that involve month-end calculations, such as generating end-of-month reports, calculating deadlines, and validating date ranges within a month.