MySQL LAST_DAY Function

MySQL LAST_DAY Function

 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 the last day of the month for the input date.

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

Let’s take some examples of using the LAST_DAY() function.

A) 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');

Here is the output:

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

B) Using MySQL 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());

The output is:

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

C) Using the MySQL 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);

Here is the output:

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

D) 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;

The output is:

+------------+ | first_day | +------------+ | 2017-07-01 | +------------+ 1 row in set (0.00 sec)

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

You then can call the FIRST_DAY() function as shown in the following query:

SELECT FIRST_DAY('2017-02-15');

This is the output:

+-------------------------+ | FIRST_DAY('2017-02-15') | +-------------------------+ | 2017-02-01 | +-------------------------+ 1 row in set (0.00 sec)

In this tutorial, you have learned how to use the MySQL LAST_DAY() function to get the last day of a month for a specified date.

Reactions

Post a Comment

0 Comments

close