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

Posted on 2006-05-12
Last Modified: 2006-11-18
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


Question by:GabicusC
    LVL 19

    Accepted 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,


    Author Comment


    That is perfect.  Thanks a lot.

    LVL 19

    Expert Comment

    Your welcome, Rich.  Glad to help.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    In this article—a derivative of my blog post (—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
    Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

    731 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now