Lynn Pettis (9/12/2012)
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;
I have a general distrust for the "wk" datepart even though it doesn't seem to matter matter here. With that thought in mind, here's a bit of code that uses a zero based Tally Table. It can be easily modified to handle a unit based Tally Table if needed. If nothing else, it makes for some really simple code.
SELECT DATEADD(dd,DATEDIFF(dd,-1,GETDATE())/7*7+(t.N),-1)
FROM dbo.Tally t
WHERE t.N BETWEEN 0 AND 6
--Jeff Moden
Change is inevitable... Change for the better is not.