MySQL LAST_DAY Function

Summary: this tutorial introduces you to the MySQL LAST_DAY()function and shows you how to apply the LAST_DAY() function in various contexts.

Introduction to MySQL LAST_DAY() function

The LAST_DAY() function takes a DATE or DATETIME value and returns a value corresponding to the last day of the month.

1	LAST_DAY(date);

The date argument must be a valid DATE or DATETIME value.

The LAST_DAY() function returns NULL if the date is zero ( 0000-00-00), invalid, or NULL.

MySQL LAST_DAY() function examples

The following example returns the last day of February 03, 2016.

1 2 3 4 5 6 7

mysql> SELECT LAST_DAY('2016-02-03'); +------------------------+ | LAST_DAY('2016-02-03') | +------------------------+ | 2016-02-29             | +------------------------+ 1 row in set (0.00 sec)

To get the last day of the current month, you combine the LAST_DAY() function with the NOW() or CURDATE() function as follows:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

mysql> SELECT LAST_DAY(NOW()); +-----------------+ | LAST_DAY(NOW()) | +-----------------+ | 2017-07-31      | +-----------------+ 1 row in set (0.00 sec) mysql> SELECT LAST_DAY(CURDATE()); +---------------------+ | LAST_DAY(CURDATE()) | +---------------------+ | 2017-07-31          | +---------------------+ 1 row in set (0.00 sec)

To get the last day of the next month, you add 1 month to the current date and apply the LAST_DAY()function:

1 2 3 4 5 6 7

mysql> SELECT LAST_DAY(CURDATE() + INTERVAL 1 MONTH); +----------------------------------------+ | LAST_DAY(CURDATE() + INTERVAL 1 MONTH) | +----------------------------------------+ | 2017-08-31                             | +----------------------------------------+ 1 row in set (0.00 sec)