[Sql Server] 取得周幾的日期

DECLARE @TestDate DATETIME;

SET @TestDate = CONVERT(varchar(10),convert(datetime,'2015/07/29'),111)

SELECT DATEADD(wk,DATEDIFF(wk,0,@TestDate),-1) as 日期, 'Last Sunday' as '周幾'

UNION SELECT DATEADD(wk,DATEDIFF(wk,0,@TestDate),-2) as 日期, 'Last Saturday' as '周幾'

UNION SELECT DATEADD(wk,DATEDIFF(wk,0,@TestDate),0) as 日期, 'Monday' as '周幾'

UNION SELECT DATEADD(wk,DATEDIFF(wk,0,@TestDate),+1) as 日期, 'Tuesday' as '周幾'

UNION SELECT DATEADD(wk,DATEDIFF(wk,0,@TestDate),+2) as 日期, 'Wednesday' as '周幾'

日期	周幾
2015-07-25 00:00:00.000	Last Saturday
2015-07-26 00:00:00.000	Last Sunday
2015-07-27 00:00:00.000	Monday
2015-07-28 00:00:00.000	Tuesday
2015-07-29 00:00:00.000	Wednesday