CODEWITHSUNDEEP
sqlmysql
Lennie De Villiers
Lennie De Villiers

Reputation: 751

Get the first and last date of next month in MySQL

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

Upvotes: 31

Views: 59740

Answers (11)

Manish Kumar Jaiswal MJ
Manish Kumar Jaiswal MJ

Reputation: 105

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)

Upvotes: 0

JibinNajeeb
JibinNajeeb

Reputation: 844

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)

Upvotes: 1

user966380
user966380

Reputation: 7

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

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

select year(getdate())

Upvotes: 0

Pacerier
Pacerier

Reputation: 89783

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

Upvotes: 20

Peter Tillemans
Peter Tillemans

Reputation: 35341

In postgresql you have perfect date truncation functions.

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

Upvotes: 0

OMG Ponies
OMG Ponies

Reputation: 332781

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

Upvotes: 39

LateJ
LateJ

Reputation: 1

Shorter query:

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

Upvotes: 0

Stéphane
Stéphane

Reputation: 3934

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));

Upvotes: 2

DBTutor
DBTutor

Reputation: 315

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

Upvotes: 0

Daniel Vassallo
Daniel Vassallo

Reputation: 344561

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)

Upvotes: 30

zed_0xff
zed_0xff

Reputation: 33257

# 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

Upvotes: 3

Related Questions