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 | +---------------------+-------------------+