MySQL LAST_DAY() – Find the last day of a month in MySQL

In this tutorial, we will learn about the MySQL LAST_DAY() function. Finding the last day of a month has many applications while working with databases. Different months have a different last day. Some months have 30 days, some have 31 days and February has 28 days in a non-leap year while 29 days in a leap year.

MySQL provides us with the MySQL LAST_DAY() function. The LAST_DAY() function takes in a date or a datetime value as an argument and returns the date of the last day of that month. Suppose you want to know the last day of the month for the date – 2020-05-14.

Simply pass this date to the LAST_DAY() function as a parameter. The MySQL LAST_DAY() returns the value 31, which is the number of days in the month of May. Let us explore this function below by understanding its syntax and then looking at a few examples.

Syntax of MySQL LAST_DAY()

LAST_DAY(date)Code language: SQL (Structured Query Language) (sql)

Where, ‘date’ is a required parameter which contains the date or datetime value from which you want to extract the last day of the month.

Examples of MySQL LAST_DAY()

Let us kick things off with a couple of basic examples. I have the following two dates – “2021-01-05” and “2020-04-24”. I want to know the last day of the month for these two dates. How do we go about this? We will use the LAST_DAY() function with the SELECT statement in the query below –

SELECT LAST_DAY("2021-01-05"); 
SELECT LAST_DAY("2020-04-24");Code language: SQL (Structured Query Language) (sql)

And the output is –

MySQL LAST_DAY() With Leap Year Date

We all know what a leap year is. It is a year that occurs once in 4 years which has an extra day in it, added to the 28 days in February – making it 29 days. Let us see an example of this. We will pass two similar dates to the LAST_DAYS() function in separate SELECT statements. One date will have a leap year while the other will be a non-leap year date. The dates are – “2020-02-14” and “2021-02-14”. The queries are –

SELECT LAST_DAY("2020-02-14"); 
SELECT LAST_DAY("2021-02-14");Code language: SQL (Structured Query Language) (sql)

And the output is –

MySQL LAST_DAY() With Wrong Date Value

What if we pass a wrong date to the LAST_DAY() function? A wrong date value is one in which the month value is greater than 12 or the day value is greater than 31. LAST_DAY() returns NULL if a wrong date value is passed to it.

SELECT LAST_DAY("2021-16-02");Code language: SQL (Structured Query Language) (sql)

And the output is –

MySQL LAST_DAY() With Datetime Values

As I mentioned in the syntax, we can also pass datetime values to the LAST_DAY() function. Let us see an example of this. Consider the below query where we pass the datetime value “2021-03-02 15:44:19” to the LAST_DAY() function.

SELECT LAST_DAY("2021-03-02 15:44:19");Code language: SQL (Structured Query Language) (sql)

And the output is –

LAST_DAY() With CURDATE() and NOW() Functions

We can also pass functions like CURDATE() and NOW() as the parameter to the LAST_DAY() function. Let us see an example of LAST_DAY() with CURDATE(). We will use MySQL aliases in this query to make our output more readable.

SELECT CURDATE() AS 'Current Date', LAST_DAY(CURDATE()) AS 'Last Day Of This Month';Code language: SQL (Structured Query Language) (sql)

And the output is –

Similarly, with the NOW() function, the query is –

SELECT NOW() AS 'Current Date & Time', LAST_DAY(NOW()) AS 'Last Day Of This Month';Code language: SQL (Structured Query Language) (sql)

And the output is –

Using LAST_DAY() To Find Last Day of the Next Month

Yep, you heard that right! We can use LAST_DAY() to find the last day of the next month too. But it is not as straightforward as you think. There are of course some ingredients aka functions that need to be added to it. Below is a query in which we pass the CURDATE() function to the LAST_DAY() function. However, notice that we have added “INTERVAL 1 MONTH” to the CURDATE() function. This adds one month to the date returned by CURDATE() and then the LAST_DAY() function finds the last day of this new date. Let us look at an example of this.

SELECT LAST_DAY(CURDATE() + INTERVAL 1 MONTH);Code language: SQL (Structured Query Language) (sql)

And the output is –

Working With Tables

Consider the below Employee table.

Let us write a query that displays the employee ID, their name, their joining date and the last day of the month of their joining date. The query is –

SELECT eid, Name, Date_Joined, LAST_DAY(Date_Joined) FROM Employee;Code language: SQL (Structured Query Language) (sql)

And the output is –

Conclusion

The MySQL LAST_DAY() function is used to find the last date of the month for a given time/datetime value. I would encourage you to practice different examples of it.

References

• MySQL Official Documentation on the LAST_DAY() function.