Lynn Pettis (9/11/2012)
Here is a bit of code that returns what you are looking for:
declare @TestDate date = '2012-09-11';
with SevenRows(n) as (select row_number() over (order by (select null)) - 1 from (values (1),(1),(1),(1),(1),(1),(1))dt(n))
select dateadd(dd, n,dateadd(wk,datediff(wk,0,dateadd(dd,-1,@TestDate)),0)) from SevenRows;
Code rewritten for SQL Server 2005:
declare @TestDate date = '2012-09-11';
with SevenRows(n) as (select
row_number() over (order by (select null)) - 1
from (select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1)dt(n))
select dateadd(dd, n,dateadd(wk,datediff(wk,0,dateadd(dd,-1,@TestDate)),0)) from SevenRows;