MySQL - LAST_DAY() Function

The MySQL LAST_DAY() function is used to retrieve the last day of the month in the given date or datetime expression. This function returns a string value. But, if the given argument is invalid or NULL, it returns NULL.

For instance, the last day of the February month in 2023 is '2023-02-28', hence, this functions returns the same if you any date of the month as an argument to it.

Syntax

Following is the syntax of MySQL LAST_DAY() function −

LAST_DAY(date);

Parameters

This method accepts the date value as a parameter.

Return value

This function returns the the last day of the month in the given date or datetime expression.

Example

Following example demonstrates the usage of the MySQL LAST_DAY() function −

SELECT LAST_DAY('2019-05-25') As Result;

Output

This will produce the following result −

Result
2019-05-31

Example

We can also pass the date-time expression as an argument to this function −

SELECT LAST_DAY('2015-09-05 09:40:45.2300') As Result;

Output

This will produce the following result −

Result
2015-09-30

Example

In the following example we are retrieving last day of the month value from the current date −

SELECT LAST_DAY(CURDATE()) As Result;

Output

This will produce the following result −

Result
22023-11-30

Example

If the month part in the given date is 0, this function returns NULL −

SELECT LAST_DAY('2017-00-01') As Result;

Following is the output −

Result
NULL

If we pass an empty string or a non-string value as an argument this function returns NULL.

SELECT LAST_DAY('') As Result;

Following is the output −

Result
NULL

Here, we are passing a non-string value as an argument to this function −

SELECT LAST_DAY(1990-11-11) As Result;

Following is the output −

Result
NULL

Example

Let us create a table with name ORDERS using CREATE statement as shown below − 

CREATE TABLE ORDERS (
   OID INT NOT NULL,
   DATE VARCHAR (20) NOT NULL,
   CUSTOMER_ID INT NOT NULL,
   AMOUNT DECIMAL (18, 2)
);

Now, insert the following records into the ORDERS table using the INSERT statement −

INSERT INTO ORDERS VALUES 
(102, '2009-10-08 00:00:00', 3, 3000.00),
(100, '2009-10-08 00:00:00', 3, 1500.00),
(101, '2009-11-20 00:00:00', 2, 1560.00),
(103, '2008-05-20 00:00:00', 4, 2060.00);

Execute the below query to fetch all the inserted records in the above-created table −

Select * From ORDERS;

Following is the ORDERS table −

OID DATE CUSTOMER_ID AMOUNT
102 2009-10-08 00:00:00 3 3000.00
100 2009-10-08 00:00:00 3 1500.00
101 2009-11-20 00:00:00 2 1560.00
103 2008-05-20 00:00:00 4 2060.00

Now, we use the MySQL LAST_DAY() function to retrieve the last day of the month from all the datetime values in the DATE column −

SELECT OID, DATE, LAST_DAY(DATE) As Result From ORDERS;

Output

The output is displayed as follows −

OID DATE Result
102 2009-10-08 00:00:00 2009-10-31
100 2009-10-08 00:00:00 2009-10-31
101 2009-11-20 00:00:00 2009-11-30
103 2008-05-20 00:00:00 2008-05-31
Advertisements