Link to home
Start Free TrialLog in
Avatar of Richard Comito
Richard ComitoFlag for United States of America

asked on

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

------------------------------------------------------------------------------------------

Thanks
ASKER CERTIFIED SOLUTION
Avatar of folderol
folderol

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Richard Comito

ASKER

Tom,

That is perfect.  Thanks a lot.

Rich
Avatar of folderol
folderol

Your welcome, Rich.  Glad to help.