获取某一年某周的时间范围算法

Question

select getdate()-datepart(dw,getdate())+1,getdate()+7-datepart(dw,getdate())

Answer 1

--如果按星期天是第一天的话
select 开始时间=getdate()-datepart(dw,getdate())+1,结束时间=getdate()+7-datepart(dw,getdate())
/*
开始时间                                                   结束时间                                                   
------------------------------------------------------ ------------------------------------------------------ 
2009-03-29 20:39:49.420                                2009-04-04 20:39:49.420
*/

Answer 2

select dateadd(wk,datediff(wk,0,getdate()),0) 周一,dateadd(wk,datediff(wk,0,getdate()),0)+6 周日
周一                      周日
----------------------- -----------------------
2009-03-30 00:00:00.000 2009-04-05 00:00:00.000(1 行受影响)

Answer 3

错了,以上是按周一为第一天,以下按周日为第一天:
select dateadd(wk,datediff(wk,0,getdate()),0)-1 周日,dateadd(wk,datediff(wk,0,getdate()),0)+6-1 周六
周日                      周六
----------------------- -----------------------
2009-03-29 00:00:00.000 2009-04-04 00:00:00.000(1 行受影响)

Answer 4

DECLARE @Year INT,@Week INT;
SELECT @Year=2009,@Week=14;DECLARE @date DATETIME;SET @date=DATEADD(week,@Week-1,DATEADD(year,(@Year-1900),0));SET @date=DATEADD(day,DATEPART(weekday,@date)+2,@date)SET @date=DATEADD(day,-DATEPART(weekday,@date)+1,@date)SELECT @date AS start_dt,@date+6/*
start_dt                
----------------------- -----------------------
2009-04-05 00:00:00.000 2009-04-11 00:00:00.000(1 行受影响)
*/