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?

SELECT DATE_ADD(LAST_DAY(NOW()),INTERVAL 1 DAY) AS 'FIRST DAY OF NEXT MONTH'; SELECT DATE_ADD(LAST_DAY(NOW()),INTERVAL 1 MONTH) AS 'LAST DAY OF NEXT MONTH' — Manish Kumar Jaiswal MJ, Aug 25 '20 at 09:34

score 39 · Accepted Answer · edited Mar 10 '15 at 15:06

39

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

edited Mar 10 '15 at 15:06

Pacerier

86,231
106
366
634

answered Jun 09 '10 at 21:11

OMG Ponies

325,700
82
523
502

For more MySQL time-related functions like this one, see [this blog post](http://sys-exit.blogspot.com/2013/06/mysql-today-tomorrow-yesterday-this.html) – ducin Mar 14 '14 at 21:27
@OMGPonies, Use `curdate` instead of `now()` because we don't need the timings. Also, for first day, it's more straightforward to [simply minus the number of days of the current month](http://stackoverflow.com/a/28844641/632951). It's also faster because we are doing only a single `date_sub()` call and a single `day()` call. – Pacerier Mar 04 '15 at 00:32

Daniel Vassallo · Answer 2 · 2010-06-09T21:21:30.513

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)

@Daneil, DATE_FORMAT returns you a `var_string`, not a date. Use my solution instead. — Pacerier, Mar 10 '15 at 15:07

score 19 · Answer 3 · edited May 23 '17 at 12:26

First day of next month is simply last day of this month + 1:

select adddate(last_day(curdate()), 1)

Last day of next month is simply last day of (today + 1 month):

select last_day(curdate() + interval 1 month))

These are the most straightforward solutions. You'll not be able to find a shorter one.

If you need the first day of the current month, see https://stackoverflow.com/a/28966866/632951

score 3 · Answer 4 · answered Jun 09 '10 at 21:05

# 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

score 2 · Answer 5 · answered Jun 22 '12 at 14:58

2

As @DanielVassallo explained it, retrieving the last day of next month is easy:

SELECT LAST_DAY(DATE_ADD(CURRENT_DATE(), INTERVAL 1 MONTH));

To retrieve the first day, you could first define a custom FIRST_DAY function (unfortunately MySQL does not provide any):

DELIMITER ;;
CREATE FUNCTION FIRST_DAY(day DATE)
RETURNS DATE DETERMINISTIC
BEGIN
  RETURN ADDDATE(LAST_DAY(SUBDATE(day, INTERVAL 1 MONTH)), 1);
END;;
DELIMITER ;

And then you could do:

SELECT FIRST_DAY(DATE_ADD(CURRENT_DATE(), INTERVAL 1 MONTH));

answered Jun 22 '12 at 14:58

Stéphane

3,884
1
30
27

1

Can't we simply write (last_day(current_date() + interval 1 month)) ? – Uncle Iroh Jun 05 '14 at 18:47
@UncleIroh, Yes we can, that works too. But there's actually an **even shorter** solution: http://stackoverflow.com/a/28844641/632951 – Pacerier Mar 10 '15 at 15:21

score 1 · Answer 6 · answered Feb 12 '18 at 09:12

1

To get first date of next month :-select LAST_DAY(NOW()) + INTERVAL 1 DAY

To get last date of next month :-select LAST_DAY(NOW()+ INTERVAL 1 Month)

answered Feb 12 '18 at 09:12

JibinNajeeb

784
1
10
31

score 0 · Answer 7 · answered May 09 '12 at 12:08

Here is all Possible solutions. Hope this will help ... Just Run this to get All details

SELECT 

  DATE_SUB(DATE_FORMAT(NOW(),'%Y-%m-%d'),INTERVAL 1 DAY) AS GetLastDay, 
  DATE_FORMAT(NOW(),'%Y-%m-%d') AS GetTodaysDate,
  DATE_ADD(DATE_FORMAT(NOW(),'%Y-%m-%d'),INTERVAL 7 DAY) AS Add7DaysFromTodaysDate,
  DATE_SUB(DATE_FORMAT(NOW(),'%Y-%m-%d'),INTERVAL 7 DAY) AS Substract7DaysFromTodaysDate,
  DATE_ADD(DATE_FORMAT(NOW(),'%Y-%m-%d'),INTERVAL 1 MONTH) AS Add1MonthFromTodaysDate,
  DATE_FORMAT(NOW(),'%Y-%m-01') AS FirstDayCurrentMonth , 
  LAST_DAY(DATE_FORMAT(NOW(),'%Y-%m-%d')) AS lastDayCurrentMonth,
  DATE_SUB(LAST_DAY(DATE_SUB(NOW(), INTERVAL 1 MONTH)), 
            INTERVAL DAY(LAST_DAY(DATE_ADD(NOW(), INTERVAL 1 MONTH)))-1 DAY) AS FirstOfNextMont007,
  LAST_DAY(DATE_SUB(NOW(), INTERVAL 1 MONTH)) AS lastOfpREMonth,
  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

score 0 · Answer 8 · answered Oct 17 '14 at 09:51

0

Shorter query:

SELECT
ADDDATE(LAST_DAY(NOW()), 1) AS firstOfNextMonth,
LAST_DAY(DATE_ADD(NOW(), INTERVAL 1 MONTH)) AS lastOfNextMonth

answered Oct 17 '14 at 09:51

LateJ

1
1

[`curdate` is better](http://stackoverflow.com/a/28844641/632951) than `now` in terms of both speed and intention conveyance. Also, `+` is better than `DATE_ADD` in terms of both readability and command length. – Pacerier Mar 10 '15 at 15:19

score 0 · Answer 9 · edited Mar 10 '15 at 15:11

0

In postgresql you have perfect date truncation functions.

For MySQL, I found a discussion here which might give some ideas.

edited Mar 10 '15 at 15:11

Pacerier

86,231
106
366
634

answered Jun 09 '10 at 21:11

Peter Tillemans

34,983
11
83
114

Good link actually, but you want to include the contents of it within this page. What do you mean by date truncation functions anyway? – Pacerier Mar 10 '15 at 15:10

Manish Kumar Jaiswal MJ · Answer 10 · 2020-08-25T09:38:09.557

SELECT DATE_ADD(LAST_DAY('2020-12-14'),INTERVAL 1 DAY) AS 'FIRST DAY OF NEXT MONTH';
        +----------------------------+
        | FIRST DAY OF NEXT MONTH |
        +----------------------------+
        | 2021-01-01                 |
        +----------------------------+
        1 row in set (1.06 sec)
        
SELECT DATE_ADD(LAST_DAY(NOW()),INTERVAL 1 MONTH) AS 'LAST DAY OF NEXT MONTH';
        +------------------------+
        | LAST DAY OF NEXT MONTH |
        +------------------------+
        | 2020-09-30             |
        +------------------------+
        1 row in set (0.51 sec)

score 0 · Answer 11 · edited May 14 '15 at 08:19

0

select Convert(varchar(10),DateAdd(Day, 1, getdate() - Day(getdate()) + 1) -1,105)

select Convert(varchar(10),getdate(),105)

select year(getdate())

edited May 14 '15 at 08:19

shA.t

16,580
5
54
111

answered Mar 30 '12 at 07:04

user966380

7
1

1

I think your answer is for SQL Server ;). – shA.t May 14 '15 at 08:19

Get the first and last date of next month in MySQL

11 Answers11

Linked