Get First Day and Last Day of Current and Last Month MySQL

In this tutorial, we would love to share with you how to get the first day of the month, the last day of the month, the first day of the previous month, the last day of the previous month Mysql. We will demonstrate with examples.

If you need to get the first and last day of the current, next and previous month, so you will learn here with demonstrate each example using MySQL queries.

Table of Content

First day of Current Month
Last day of Current Month
Next Month Last Day
First day of Previous Month
Last day of Previous Month

First day of Current Month

Now let’s take an example to explain.

SELECT LAST_DAY(curdate() - interval 1 month) + interval 1 day

 =========================OR======================================

 SELECT DATE_SUB(LAST_DAY(NOW()),INTERVAL DAY(LAST_DAY(NOW()))-
 1 DAY) AS 'FIRST DAY OF CURRENT MONTH';

Output-1

+-------------+
| Result      |
+-------------+
| 2019-07-01  |
+-------------+

Last day of Current Month

Let’s take another example to get the last day of the current month in mysql. We will use MySQL now() or curdate() function with last_day().

select last_day(now())

 =========================OR====================================== 

select last_day(curdate())

Output

+-------------+
| Result      |
+-------------+
| 2019-07-31  |
+-------------+

Next Month Last Day

In this example, we will get the last day of next month using this function.

We will add one month in the current date-time value and pass the value in LAST_DAY() function. It will return on the last day of next month. See the query of the following :

SELECT LAST_DAY(CURDATE() + INTERVAL 1 MONTH);

Output

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

First day of Previous Month

Let’s take a new example, we get the first day of the previous month.

select last_day(curdate() - interval 2 month) + interval 1 day

Output

+---------------------+
| result              |
+---------------------+
| 2019-06-01          | 
+---------------------+

Last day of Previous Month

Let’s take the next example, we get last day of the previous month.

select last_day(curdate() - interval 1 month)

Output

+-------------+
| Result      |
+-------------+
| 2019-06-01  |
+-------------+

Conclusion

Here, you have learned how to use MySQL LAST_DAY() function get the first day of the month , last day of the month, first and last day of the previous month.