Can I get the First Day of the week and Week number without calling from a table?

I have the following Query that I am trying to get the Week Number and First Day of that week.  The way the query is now I need to call a date from a Table and use that date to come up with the information that I want.  What I would like to do is have the results of the select statement to be of a date span without pulling any information from a Table?  So, I would like all the Week Numbers and The first Day of each week for the date span of 05/01/2006 thru 06/01/2006 as the results.

--------------------------  SQL  -----------------------------------------------------

select distinct
      datepart(wk, fldActiveDate) as fldweek,
      convert(varchar, dateadd(dd,
            1-      (case datepart(dw, fldActiveDate)
                        when 1 then 1
                        else datepart(dw, fldActiveDate) end),
                        convert(datetime, fldActiveDate, 110)
                        ), 110) fldFirstDay
from tblBannerAd
where fldActiveDate < '05/08/2006'
and fldCancelDate > '05/08/2006'
and idBannerdesc between 4 and 6


Richard ComitoDirector of ITAsked:
Who is Participating?
declare @week as int
declare @start as datetime
declare @end as datetime
declare @monday as datetime

set @start = '20060501'
set @end = '20060601'
set @monday = dateadd(day, 2 - datepart(weekday,@start),@start)

/*** next line is debug info so it is commented out ***/
-- select @monday, datepart(week,@start), datepart(week,@end)

create table #week_rows
      week      int      null,
      Monday      datetime      null,

set @week = datepart(week,@start)

while @week <= datepart(week,@end)
insert into #week_rows(week,Monday) VALUES(@week, @monday)
set @week = @week + 1
set @monday = dateadd(day, 7, @monday)

select * from #week_rows

/*** Delete the temporary table, only necessary in testing phase  ***/
--drop table #week_rows

Hey GabicusC,

This is a self-contained test.  Cut and paste the code above into Query Analyzer, and run it to see what it does.  You can output directly from within the while loop without first inserting to a table, but I assume a temp table is more appropriate.  Ask any follow-up questions and I will help if I can,

Richard ComitoDirector of ITAuthor Commented:

That is perfect.  Thanks a lot.

Your welcome, Rich.  Glad to help.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.