When using MySQL, you can use the LAST_DAY() function to return the last day of a given month.

You can provide the current date, or you can provide another specified date (for example, a date from a column in a database), and LAST_DAY() will return the last day of the month for that date.

Syntax

The syntax goes like this:

LAST_DAY(date)

Where date is the date for which you want the last day to be returned.

Example 1 – Basic Usage

Here’s an example to demonstrate the way it works.

SELECT LAST_DAY('2021-01-01');

Result:

+------------------------+
| LAST_DAY('2021-01-01') |
+------------------------+
| 2021-01-31             |
+------------------------+

So it takes the date we provide as an argument, and then returns the last day of that month.

Example 2 – Leap Years

As you might expect, it’s smart enough to be able to deal with leap years.

Non-Leap Year

SELECT LAST_DAY('2021-02-01');

Result:

+------------------------+
| LAST_DAY('2021-02-01') |
+------------------------+
| 2021-02-28             |
+------------------------+

Leap Year

SELECT LAST_DAY('2020-02-01');

Result:

+------------------------+
| LAST_DAY('2020-02-01') |
+------------------------+
| 2020-02-29             |
+------------------------+

Example 3 – Datetime Values

You can also provide a datetime value.

SELECT LAST_DAY('2021-02-01 23:59:15');

Result:

+---------------------------------+
| LAST_DAY('2021-02-01 23:59:15') |
+---------------------------------+
| 2021-02-28                      |
+---------------------------------+

Example 4 – Invalid Values

If you provide an invalid date, a null value will be returned.

SELECT LAST_DAY('2021-02-32');

Result:

+------------------------+
| LAST_DAY('2021-02-32') |
+------------------------+
| NULL                   |
+------------------------+
1 row in set, 1 warning (0.00 sec)

Example 5 – A Database Example

Here’s an example of this function being used in a database query.

USE sakila;
SELECT
  payment_date AS 'Date/Time',
  LAST_DAY(payment_date) AS 'Last day of month'
FROM payment
WHERE payment_id = 1;

Result:

+---------------------+-------------------+
| Date/Time           | Last day of month |
+---------------------+-------------------+
| 2005-05-25 11:30:37 | 2005-05-31        |
+---------------------+-------------------+