select that iterates through each week

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

select that iterates through each week

Author

Topic

dcummiskey
Starting Member

26 Posts

Posted - 2004-10-19 : 18:41:32

Hello -
I'm just trying to write a select statement that would return the date for each sunday(1st day of week) for the year. So far I have:

select dateadd(day, 7, DATEADD(wk, DATEDIFF(wk,0,getdate()), -1))

I'm guessing I need to somehow iterate through each week and pass that in where the getdate value is...is it possible to do this with another select?

thanks in advance.
Dan

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-10-19 : 23:49:00

you will need a numbers table (tally table)

ie


declare @numbers table (n identity(0,1), blah int)
Insert Into @numbers
Select blah = 1 From master.dbo.syscolumns

Declare @startDay = '1/4/2004'

--note that n starts at 0
Select sunday = @startDay + 7*n 
From @numbers
Where year(@startDay + 7*n) = year(getdate())

Corey

VIG
Yak Posting Veteran

86 Posts

Posted - 2004-10-20 : 00:27:49

[code]select dateadd(d,100*a+10*b+c,str(year(getdate()),4,0)+'0101')
from
(select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all
select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a,
(select 0 as b union all select 1 union all select 2 union all select 3 union all select 4 union all
select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b,
(select 0 as c union all select 1 union all select 2 union all select 3 union all select 4 union all
select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
where year(dateadd(d,100*a+10*b+c,str(year(getdate()),4,0)+'0101'))=year(getdate())
and datename(dw,dateadd(d,100*a+10*b+c,str(year(getdate()))+'0101'))='sunday'
order by 1[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources