How to Get Last Day of the Month in MySQL

In this article, You will learn how to get last day of the month in MySQL with the help of the example. This requirement might happen in real-life projects where you need to take an action on the basis of the last day of the specified Date or DateTime.

To get the last day of the month, MySQL provides a date function called LAST_DAY().

Headings of Contents

1 MySQL LAST_DAY() Function
- 1.1 Syntax
2 Using MySQL LAST_DAY() Function?
3 Conclusion

MySQL LAST_DAY() Function

In MySQL, LAST_DAY() function is used to get the last day of the specified month. It returns NULL if the specified month is Invalid or NULL.

Syntax

The syntax of LAST_DAY() function is:


LAST_DAY(month)

let’s see, some examples of MySQL LAST_DAY() function to get the last day of the specified month.

Using MySQL LAST_DAY() Function?

Here, I have taken a few examples to understand the LAST_DAY() function to get the last day of the specified month.

Example

In this example, I have got the last day of the month of January.

mysql> SELECT LAST_DAY('2023-01-12') as 'LAST DAY';
+------------+
| LAST DAY   |
+------------+
| 2023-01-31 |
+------------+
1 row in set (0.17 sec)

Example: Using LAST_DAY() with invalid column

In this example, I have passed the invalid date in LAST_DAY() function to get the last day of the month of January.

mysql> SELECT LAST_DAY('2023-01-45') as 'LAST DAY';
+----------+
| LAST DAY |
+----------+
| NULL     |
+----------+
1 row in set, 1 warning (0.01 sec)

As you can see in the above output, I have received the NULL output because we know that number very clearly The number of days available in January is only 31, but I have passed 45 that’s why I am getting a NULL value.

Example:Using LAST_DAY() with NULL value

In this example, I have used LAST_DAY() function with a NULL value.

mysql> SELECT LAST_DAY(NULL) as 'LAST DAY';
+----------+
| LAST DAY |
+----------+
| NULL     |
+----------+
1 row in set (0.00 sec)

Example: Using LAST_DAY() function with date column

To apply the LAST_DAY() function on the date column, I have created a table called employee having some records. The employee table has a column joining_date having to the join date of the employee and now I want to create another column that will contain the last day of the joining month of each employee.

As you can see in the below Query, How I have applied the LAST_DAY() function on the joining_date column to get the last day of the corresponding month.

SELECT 
    id,
    first_name,
    last_name,
    designation,
    joining_date,
    LAST_DAY(joining_date) AS 'last_day'
FROM
    employee;

Get Last Day of the Month in MySQL — **Using LAST_DAY() function with a date column**

The same SQL Query has been written and executed inside MySQL Workbench.

Example

Here, I have used hardcoded string date time to get the last day of the month.

mysql> SELECT LAST_DAY('2023-01-12 5:30:10') as 'LAST DAY';
+------------+
| LAST DAY   |
+------------+
| 2023-01-31 |
+------------+
1 row in set (0.00 sec)

👉MySQL LAST_DAY()Function Ref:- Click Here

Related Articles:

Conclusion

So, In this article, we have seen how to get last day of the month in Mysql with the help of various examples. This function is going to be very helpful, especially when you are working on any real-life project or any kind of data analysis project. I hope you found this article helpful, Please share and keep visiting for further interesting MySQL tutorials.

Have a nice day.

◀How to extract month from date in MySQL

How to Find the Second Highest Salary in MySQL▶