Autopopulate a 'dates' table

[markSaunders]

Hi all,

Any ideas on a simple piece of code (i would hope!) to populate a table with weekly dates.

I have to store the date for the Friday of every week, 6 months in advance (at least) and can't get my head round how to automate this

any ideas would be splendid!

thanks
m Mark Saunders

 

[skuhlman]

Here's some code to get the next 26 fridays. Once you get the list, you can compare it to your existing table to get only those fridays which aren't already in the existing table and insert them.

declare @FirstFriday datetime
set @FirstFriday = convert(datetime,convert(char (10),getdate(),121))
while datepart(dw,@FirstFriday)<>6
set @FirstFriday = dateadd(dd,1,@FirstFriday)
print @FirstFriday

declare @MyPointer integer
set @MyPointer = 0
create table #TempDateList (mydate datetime)
while @MyPointer < 26
begin
insert into #TempDateList (mydate )
values (dateadd(dd,@MyPointer*7,@FirstFriday))
set @MyPointer = @MyPointer+1
end

select * from #TempDateList
drop table #TempDateList