MySQL FIRST_DAY Function

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.

• Get the last day of the month of a date.
• Add 1 day to get the first day of the next month using DATE_ADD() function
• 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 2017-07-14.

1 2 3 4 5 6 7 8 9 10 11

SELECT     DATE_ADD(DATE_ADD(LAST_DAY('2017-07-14'),             INTERVAL 1 DAY),         INTERVAL - 1 MONTH) AS first_day; +------------+ | 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:

1 2 3 4 5 6 7 8

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 follows:

1 2 3 4 5 6 7

mysql> SELECT FIRST_DAY('2017-02-15'); +-------------------------+ | 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.