在SQL Server中获取每周给定的一周数，月份数和年份的日期范围

Question

我正在创建存储过程以基于周数，月份数和年份数在日期范围内显示某些记录。

例如：

Month = 4 [APRIL] 
Week = 2 [Week 2 for APRIL] 
Year = 2017 

输出应该是：开始一天应该是星期天]

STARTDATE | END DATE 
4-2-2017 | 4-8-2017 

到处搜寻，有这个一个已经很难。

编辑： 我提供了一个示例查询，以供参考

@Month int, 
@Year int, 
@WeekNumber int 

SELECT 
(start date of the week based on week num, month, year) as DATE1, 
(end date of the week based on week num, month, year) as DATE2 
WHERE 
StartDate = (start date of the week based on week num, month, year) 
EndDate = (end date of the week based on week num, month, year) 

Answer 1

这或许可以提供帮助。

示例更新了2008年

Declare @Month int = 4 
Declare @Year int = 2017 
Declare @Week int = 2 


Select DateR1 = min(RetVal) 
     ,DateR2 = max(RetVal) 
From (
     Select *,WkNr = Dense_Rank() over (Order by DatePart(WEEK,RetVal)) 
     From (Select Top (40) RetVal=DateAdd(DD,-1+Row_Number() Over (Order By Number),cast(str((@Year*10000)+(@Month*100)+1,8) as date)) From master..spt_values) D 
    ) A 
Where [email protected] 

返回

DateR1  DateR2 
2017-04-02 2017-04-08