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

Get first day and last day of current and last month MySQL; In this tutorial, i am going to show 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 in Mysql.

How to get first day and last day of month in MySQL

Use the following mysql queries 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; as follows:

  • 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

Use the following query to get first day of current month; as follows:

 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 use the following query to get the last day of the current month in mysql; as follows:

select last_day(now())

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

select last_day(curdate())

Output

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

Next Month Last Day

To get the last day of next month in MySQL; as follows:

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

To get the first day of the previous month in month; as follows:

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

Output

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

Last day of Previous Month

To get last day of the previous month in mysql; as follows:

select last_day(curdate() - interval 1 month)

Output

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

Conclusion

Get first day and last day of current and last month MySQL; In this tutorial, you have learned 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 in Mysql.

One Comment

  1. Robbert said:

    Very helpfull, thanks!

    January 22, 2020
    Reply

Leave a Reply

Your email address will not be published.