SQL 查询一个月的第一天和最后一天
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15742605/
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
query for first and last day of month
提问by PookPook
If I have the name of the month, how can I have the first and last day of that month in SQL?
如果我有月份的名称，我如何在 SQL 中获得该月的第一天和最后一天？
I have this query to returns the month names:
我有这个查询来返回月份名称：
DECLARE @StartDate DATETIME,
@EndDate DATETIME;
SELECT @StartDate = '20110501'
,@EndDate = '20110801';
SELECT DATENAME(MONTH, DATEADD(MONTH, x.number, @StartDate)) AS MonthName
FROM master.dbo.spt_values x
WHERE x.type = 'P'
AND x.number <= DATEDIFF(MONTH, @StartDate, @EndDate)
Result:
结果：
Now, how can i get the first and last day of that months? changing the query.
现在，我怎样才能得到那几个月的第一天和最后一天？更改查询。
回答by Sire
If you want a more general and simple solution:
如果你想要一个更通用和简单的解决方案：
DECLARE @startdate AS DATETIME = GETDATE()
SELECT
DATEADD(MONTH, DATEDIFF(MONTH, 0, @startdate) , 0) as startMonth,
DATEADD(SECOND, -1, DATEADD(MONTH, 1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @startdate) , 0) ) ) as endMonth
Gives the result:
给出结果：
startMonth endMonth
----------------------- -----------------------
2013-06-01 00:00:00.000 2013-06-30 23:59:59.000
回答by praveen
Try this :-
尝试这个 ：-
DECLARE @StartDate DATETIME,
@EndDate DATETIME;
SELECT @StartDate = '20110501'
,@EndDate = '20110801';
SELECT DATENAME(MONTH, DATEADD(MONTH, x.number, @StartDate)) AS MonthName,
CONVERT(VARCHAR(25),
DATEADD(dd,-(DAY(DATEADD(MONTH, x.number, @StartDate))-1),DATEADD(MONTH, x.number, @StartDate)),101) as FirstDay,
CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,DATEADD(MONTH, x.number, @StartDate)))),DATEADD(mm,1,DATEADD(MONTH, x.number, @StartDate))),101) as LastDay
FROM master..spt_values x
WHERE x.type = 'P'
AND x.number <= DATEDIFF(MONTH, @StartDate, @EndDate)
Result :-
结果 ：-
MonthName FirstDay LastDay
May 05/01/2011 05/31/2011
June 06/01/2011 06/30/2011
July 07/01/2011 07/31/2011
August 08/01/2011 08/31/2011
Result obtained taking the help from thisquery
从这个查询的帮助中获得的结果
回答by Milen
This code gives you the first date of Current Month:
此代码为您提供当月的第一个日期：
DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
If you know the Month's numberthen:
如果你知道月份的数字，那么：
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + @MonthNumber, 0)
Same goes for Month's end date:
这同样适用于本月结束日期：
SELECT DATEADD(MILLISECOND, -1,
DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - @@MonthNumber+ 1, 0))
回答by SQL Tactics
Depending on the version of SQL Server you use, you might also be able to use EOMONTH()... I wrote a guide that breaks down the options in various flavors of SQL here: How to Find the Last Day of the Month in SQL, MySQL, PostgreSQL, and Teradata
根据您使用的SQL Server版本，您可能还可以使用EOMONTH（）......我写了一个指南，打破了在SQL的各种口味的选择这里：如何查找SQL每月的最后一天、MySQL、PostgreSQL 和 Teradata
