SQL Server 2005 获取任何年份中任何月份的第一个和最后一个日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3916398/
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
SQL Server 2005 Get First and Last date for any Month in any Year
提问by Ristogod
I have a stored procedure that has to accept a month as int (1-12) and a year as int. Given those two values, I have to determine the date range of that month. So I need a datetime variable to represent the first day of that month, and another datetime variable to represent the last day of that month. Is there a fairly easy way to get this info?
我有一个存储过程，它必须接受一个月作为 int (1-12) 和一年作为 int。鉴于这两个值，我必须确定该月的日期范围。所以我需要一个 datetime 变量来表示那个月的第一天，另一个 datetime 变量来表示那个月的最后一天。有没有一种相当简单的方法来获取这些信息？
回答by DOK
First day of the month:SELECT DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)
每月的第一天：SELECT DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)
Last day of the month:SELECT DATEADD(ms, -3, DATEADD(mm, DATEDIFF(m, 0, GETDATE()) + 1, 0))
每月最后一天：SELECT DATEADD(ms, -3, DATEADD(mm, DATEDIFF(m, 0, GETDATE()) + 1, 0))
Substitute a DateTime variable value for GETDATE().
用 DateTime 变量值替换 GETDATE()。
I got that long ago from this very handy pagewhich has a whole bunch of other date calculations, such as "Monday of the current week" and "first Monday of the month".
回答by Martin Smith
DECLARE @Month int
DECLARE @Year int
set @Month = 2
set @Year = 2004
select DATEADD(month,@Month-1,DATEADD(year,@Year-1900,0)) /*First*/
select DATEADD(day,-1,DATEADD(month,@Month,DATEADD(year,@Year-1900,0))) /*Last*/
But what do you need as time component for last day of the month? If your datetimes have time components other than midnight you may well be better off just doing something like
但是您需要什么作为每月最后一天的时间组件？如果您的日期时间具有除午夜以外的时间组件，那么您最好只做类似的事情
WHERE COL >= DATEADD(month,@Month-1,DATEADD(year,@Year-1900,0))
AND COL < DATEADD(month,@Month,DATEADD(year,@Year-1900,0))
In this way your code will continue to work if you eventually migrate to SQL Server 2008 and the greater precision datetime datatypes.
这样，如果您最终迁移到 SQL Server 2008 和精度更高的日期时间数据类型，您的代码将继续工作。
回答by John Hartsock
DECLARE @Month int;
DECLARE @Year int;
DECLARE @FirstDayOfMonth DateTime;
DECLARE @LastDayOfMonth DateTime;
SET @Month = 3
SET @Year = 2010
SET @FirstDayOfMonth = CONVERT(datetime, CAST(@Month as varchar) + '/01/' + CAST(@Year as varchar));
SET @LastDayOfMonth = DATEADD(month, 1, CONVERT(datetime, CAST(@Month as varchar)+ '/01/' + CAST(@Year as varchar))) - 1;
回答by AdaTheDev
DECLARE @Month INTEGER
DECLARE @Year INTEGER
SET @Month = 10
SET @Year = 2010
DECLARE @FirstDayOfMonth DATETIME
DECLARE @LastDayOfMonth DATETIME
SET @FirstDayOfMonth = Str(@Year) + RIGHT('0' + Str(@Month), 2) + '01'
SET @LastDayOfMonth = DATEADD(dd, -1, DATEADD(mm, 1, @FirstDayOfMOnth))
SELECT @FirstDayOfMonth, @LastDayOfMonth
回答by Tim Schmelter
Try this:
尝试这个：
Declare @month int, @year int;
Declare @first DateTime, @last DateTime;
Set @month=10;
Set @year=2010;
Set @first=CAST(CAST(@year AS varchar) + '-' + CAST(@month AS varchar) + '-' + '1' AS DATETIME);
Set @last=DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@first)+1,0));
SELECT @first,@last;
回答by Crash
select [FirstDay Of The Month] as Text ,convert(varchar,dateadd(d,-(day(getdate()-1)),getdate()),106) 'Date'
union all
select [LastDay Of The Month], convert(varchar,dateadd(d,-day(getdate()),dateadd(m,1,getdate())),106)
回答by patrudu
Current month last Date:
本月最后日期：
select dateadd(dd,-day(dateadd(mm,1,getdate())),dateadd(mm,1,getdate()))
Current month 1st Date:
本月第 1 日：
select dateadd(dd,-day(getdate())+1,getdate())
回答by Lalesh Mishra
DECLARE @Month int = 3, @Year int = 2016
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
-- First date of month
SET @StartDate = DATEADD(MONTH, @Month-1, DATEADD(YEAR, @Year-1900, 0));
-- Last date of month
SET @EndDate = DATEADD(SS, -1, DATEADD(MONTH, @Month, DATEADD(YEAR, @Year-1900, 0)))
This will return first date and last date with time component.
这将返回带有时间分量的第一个日期和最后一个日期。
