在MySQL中获取下个月的第一个和最后一个日期

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

我怎样才能在下个月的第一天和最后一天在where子句中使用呢?

10 个解决方案

#1

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

#2

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

对于下个月的最后一天，可以使用LAST_DAY()函数:

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:

使用DATE_FORMAT()函数获取一个月的第一天也是一个棘手的问题。你可以这样使用:

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)

#3

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

下个月的第一天是这个月的最后一天+ 1:

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

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

下个月的最后一天就是(今天+ 1个月)的最后一天:

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://*.com/a/28966866/632951

如果您需要本月的第一天，请参见https://*.com/a/28966866/632951

#4

# 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

不确定这是最短的查询，但它们确实有效

#5

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

正如@DanielVassallo所解释的，获取下个月的最后一天很容易:

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

要检索第一天，您可以首先定义一个自定义的FIRST_DAY函数(不幸的是，MySQL没有提供任何函数):

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

#6

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

#7

Shorter query:

较短的查询:

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

#8

In postgresql you have perfect date truncation functions.

在postgresql中有完美的日期截断函数。

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

对于MySQL，我在这里找到了一个讨论，可能会给出一些想法。

#9

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

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

select year(getdate())

#10

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

要获得下个月的第一天:-选择LAST_DAY(NOW()) + INTERVAL 1天

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

下个月的最后日期:-选择LAST_DAY(现在()+间隔1个月)

#1