SQL Server 2005获取每年任何月份的第一个和最后一个日期 [英] SQL Server 2005 Get First and Last date for any Month in any Year

问题描述

我有一个存储过程，必须接受一个月作为整数（1-12）和一个一年作为整数。给定这两个值，我必须确定该月的日期范围。因此，我需要一个datetime变量来表示该月的第一天，并需要另一个datetime变量来表示该月的最后一天。

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?

推荐答案

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

如果最终迁移到SQL Server 2008和更高精度的datetime数据类型，则代码将继续有效。

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 2005获取每年任何月份的第一个和最后一个日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！