在 MySQL 中获取下个月的第一个和最后一个日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3009896/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Get the first and last date of next month in MySQL
提问by Lennie De Villiers
How can I get the first and last day of next month to be used in the where clause?
如何在 where 子句中使用下个月的第一天和最后一天？
回答by OMG Ponies
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
回答by Daniel Vassallo
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)
回答by Pacerier
First dayof next month is simply last day of this month + 1:
下个月的第一天就是这个月的最后一天+1：
select adddate(last_day(curdate()), 1)
Last dayof 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 currentmonth, see https://stackoverflow.com/a/28966866/632951
如果你需要的第一天，当前月，看到https://stackoverflow.com/a/28966866/632951
回答by zed_0xff
# 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
不确定这是最短的查询，但它们确实有效
回答by Stéphane
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_DAYfunction (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));
回答by JibinNajeeb
To get first date of next month :-select LAST_DAY(NOW()) + INTERVAL 1 DAY
要获得下个月的第一个日期：-select LAST_DAY(NOW()) + INTERVAL 1 DAY
To get last date of next month :-select LAST_DAY(NOW()+ INTERVAL 1 Month)
要获得下个月的最后日期：-select LAST_DAY(NOW()+ INTERVAL 1 Month)
回答by LateJ
Shorter query:
更短的查询：
SELECT
ADDDATE(LAST_DAY(NOW()), 1) AS firstOfNextMonth,
LAST_DAY(DATE_ADD(NOW(), INTERVAL 1 MONTH)) AS lastOfNextMonth
回答by Peter Tillemans
回答by user966380
select Convert(varchar(10),DateAdd(Day, 1, getdate() - Day(getdate()) + 1) -1,105)
select Convert(varchar(10),getdate(),105)
select year(getdate())
回答by DBTutor
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
