[Last Call] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 375
  • Last Modified:

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 Comito
Richard Comito
  • 2
1 Solution
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.

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now