해당주차 계산하여 토요일 알아내기

DECLARE @sDate varchar(10)
DECLARE @dDate DATETIME
DECLARE @sStartWeek datetime
DECLARE @sEndWeek datetime
DECLARE @nWeekOfYear INT
SET @sDate = '2011-03-21' -- 여기에 날짜 입력
SET @dDate = CAST(@sDate AS DATETIME)
print @sDate
print @dDate
print DATEPART(dw, @dDate)
print DATEPART(weekday, @dDate)
SET @sStartWeek = CONVERT(datetime, @dDate - (DATEPART(weekday, @dDate)-2), 112)
SET @sEndWeek = @sStartWeek+5
SET @nWeekOfYear = DATEPART(week, @dDate)

SELECT @sStartWeek, @nWeekOfYear, @sEndWeek -- 결과리턴

==================================

줄여서

2011-10-31 일은 월요일 해당주의 토요일은 11월 5일이다.

select CONVERT(datetime, cast('2011-10-31' as datetime) - (DATEPART(weekday, cast('2011-10-31' as datetime))-2), 112) + 5

결과 : 2011-11-05 00:00:00.000