SQL Server 2005在任何年份的任何月份都是第一次和最后一次

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变量来表示该月的最后一天。是否有一种相当简单的方法来获取这些信息?

8 个解决方案

#1

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和更精确的datetime数据类型，您的代码将继续工作。

#2

First day of the month: SELECT DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)

月的第一天:选择DATEADD(mm, DATEDIFF(mm, 0, GETDATE())， 0)

Last day of the month: SELECT DATEADD(ms, -3, DATEADD(mm, DATEDIFF(m, 0, GETDATE()) + 1, 0))

月的最后一天:选择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 page which has a whole bunch of other date calculations, such as "Monday of the current week" and "first Monday of the month".

我很久以前就从这个非常方便的页面得到了这个页面，它有很多其他的日期计算，比如“当前周的星期一”和“一个月的第一个星期一”。

#3

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

#4

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;

#5

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;

#6

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)

#7

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.

这将返回第一个日期和最后一个日期与时间组件。

#8

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

#1

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*/