Get the First And Last Date of Next Month in MySQL

Question

How can I get the first and last day of next month to be used in the where clause?

Answer 1

# FIRST date of next month
select date_sub(date_add(curdate(), interval 1 month), interval day(curdate())-1 day);

# LAST date of next month
select date_sub(date_add(curdate(), interval 2 month), interval day(curdate()) day);

not sure that's the shortest queries, but they do work

Answer 2

For the last day of next month, you can use the LAST_DAY() function:

SELECT LAST_DAY(DATE_ADD(CURDATE(), INTERVAL 1 MONTH));
+-------------------------------------------------+
| LAST_DAY(DATE_ADD(CURDATE(), INTERVAL 1 MONTH)) |
+-------------------------------------------------+
| 2010-07-31                                      |
+-------------------------------------------------+
1 row in set (0.00 sec)

Some tested edge cases:

SELECT LAST_DAY(DATE_ADD('2010-01-31', INTERVAL 1 MONTH));
+----------------------------------------------------+
| LAST_DAY(DATE_ADD('2010-01-31', INTERVAL 1 MONTH)) |
+----------------------------------------------------+
| 2010-02-28                                         |
+----------------------------------------------------+
1 row in set (0.00 sec)

SELECT LAST_DAY(DATE_ADD('2010-02-28', INTERVAL 1 MONTH));
+----------------------------------------------------+
| LAST_DAY(DATE_ADD('2010-02-28', INTERVAL 1 MONTH)) |
+----------------------------------------------------+
| 2010-03-31                                         |
+----------------------------------------------------+
1 row in set (0.00 sec)

SELECT LAST_DAY(DATE_ADD('2010-08-31', INTERVAL 1 MONTH));
+----------------------------------------------------+
| LAST_DAY(DATE_ADD('2010-08-31', INTERVAL 1 MONTH)) |
+----------------------------------------------------+
| 2010-09-30                                         |
+----------------------------------------------------+
1 row in set (0.00 sec)

There is also a tricky use of the DATE_FORMAT() function to get the first day of a month. You can use it as follows:

SELECT DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01');
+---------------------------------------------------------------+
| DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 1 MONTH),'%Y-%m-01') |
+---------------------------------------------------------------+
| 2010-07-01                                                    |
+---------------------------------------------------------------+
1 row in set (0.00 sec)

---

Therefore:

SELECT   DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01') AS
            FirstDayOfNextMonth,
         LAST_DAY(DATE_ADD(CURDATE(), INTERVAL 1 MONTH)) AS
            LastDayOfNextMonth;

+---------------------+--------------------+
| FirstDayOfNextMonth | LastDayOfNextMonth |
+---------------------+--------------------+
| 2010-07-01          | 2010-07-31         |
+---------------------+--------------------+
1 row in set (0.00 sec)

Answer 3

Use:

SELECT DATE_SUB(LAST_DAY(DATE_ADD(NOW(), INTERVAL 1 MONTH)), 
                INTERVAL DAY(LAST_DAY(DATE_ADD(NOW(), INTERVAL 1 MONTH)))-1 DAY) AS firstOfNextMonth,
       LAST_DAY(DATE_ADD(NOW(), 
                INTERVAL 1 MONTH)) AS lastOfNextMonth

Answer 4

In postgresql you have perfect date truncation functions.

I found a discussion here which might give some ideas.