取年份某周的開始日期和結束日期

1. Declare @Week varchar(20) 
2. Declare @FirstDay varchar(20) 
3. Declare @LastDay varchar(20) 
4. select @Week='200851' 
5. Declare @FirstYearDay Smalldatetime,@intWeekNum Int,@intDay int,@Day    SmallDateTime
6. SELECT @FirstYearDay=Convert(Smalldatetime,Left(@Week,4)+'/1/1'),@intWeekNum=Right(@Week,2)
7. Select @intDay=(@intWeekNum-datepart(week,@FirstYearDay))*7
8. Select @Day=DateAdd(day,@intDay,@FirstYearDay)
9. Select @FirstDay=DateAdd(Day,1-DatePart(WeekDay,@Day),@Day),@LastDay=DateAdd(Day,7-DatePart(WeekDay,@Day),@Day)
10. Select @FirstDay,@LastDay,DatePart(WeekDay,@FirstDay),DatePart(WeekDay,@LastDay),DatePart(Week,@FirstDay),DatePart(Week,@LastDay)
12. /*                                                                              
13. -------------------- -------------------- ----------- ----------- ----------- -----------
14. Dec 14 2008 12:00AM  Dec 20 2008 12:00AM  1           7           51          51
16. (1 row(s) affected)
17. */