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 the last day of the month for the input date:
LAST_DAY(date);
Code language: SQL (Structured Query Language) (sql)
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
Let’s take some examples of using the LAST_DAY()
function.
1) MySQL LAST_DAY() simple example
The following example uses the LAST_DAY()
function to return the last day of February 03, 2016
.
SELECT LAST_DAY('2016-02-03');
Code language: SQL (Structured Query Language) (sql)
Here is the output:
+------------------------+
| LAST_DAY('2016-02-03') |
+------------------------+
| 2016-02-29 |
+------------------------+
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)
2) Using LAST_DAY() function to get the last day of the current month
To get the last day of the current month, you combine the LAST_DAY()
function with the NOW()
or CURDATE()
function as follows:
SELECT LAST_DAY(NOW());
Code language: SQL (Structured Query Language) (sql)
The output is:
+-----------------+
| LAST_DAY(NOW()) |
+-----------------+
| 2017-07-31 |
+-----------------+
1 row in set (0.00 sec)
Code language: JavaScript (javascript)
SELECT LAST_DAY(CURDATE());
Code language: SQL (Structured Query Language) (sql)
+---------------------+
| LAST_DAY(CURDATE()) |
+---------------------+
| 2017-07-31 |
+---------------------+
1 row in set (0.00 sec)
Code language: JavaScript (javascript)
3) Using the LAST_DAY() function to get the last day of the next month
To get the last day of the next month, you add 1 month to the current date and pass the result to the LAST_DAY()
function as shown in the following query:
SELECT LAST_DAY(CURDATE() + INTERVAL 1 MONTH);
Code language: SQL (Structured Query Language) (sql)
Here is the output:
+----------------------------------------+
| LAST_DAY(CURDATE() + INTERVAL 1 MONTH) |
+----------------------------------------+
| 2017-08-31 |
+----------------------------------------+
1 row in set (0.00 sec)
Code language: JavaScript (javascript)
4) Getting the first day of the month for a date
MySQL does not have a function that returns the first day of a date. However, you can use the LAST_DAY()
function to calculate it by using these steps:
- First, get the last day of the month of a date.
- Second, add 1 day to get the first day of the next month using
DATE_ADD()
function - Third, subtract 1 month to get the first day of the month of the date.
The following query illustrates how to get the first day of the month of 2017-07-14
.
SELECT
DATE_ADD(DATE_ADD(LAST_DAY('2017-07-14'),
INTERVAL 1 DAY),
INTERVAL - 1 MONTH) AS first_day;
Code language: SQL (Structured Query Language) (sql)
The output is:
+------------+
| first_day |
+------------+
| 2017-07-01 |
+------------+
1 row in set (0.00 sec)
Code language: JavaScript (javascript)
To make it more convenient, you can develop a stored function named FIRST_DAY()
as follows:
DELIMITER $$
CREATE FUNCTION first_day(dt DATETIME) RETURNS date
BEGIN
RETURN DATE_ADD(DATE_ADD(LAST_DAY(dt),
INTERVAL 1 DAY),
INTERVAL - 1 MONTH);
END
Code language: SQL (Structured Query Language) (sql)
You then can call the FIRST_DAY()
function as shown in the following query:
SELECT FIRST_DAY('2017-02-15');
Code language: SQL (Structured Query Language) (sql)
This is the output:
+-------------------------+
| FIRST_DAY('2017-02-15') |
+-------------------------+
| 2017-02-01 |
+-------------------------+
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)
Summary
- Use the MySQL
LAST_DAY()
function to get the last day of a month for a specified date.