CODEWITHSUNDEEP
sqlsql-server-2008visual-studio-2008reporting-services
user2249469
user2249469

Reputation: 11

How can I generate Week ending dates (Saturdays) within a date range

I need to generate either a column in a query or a temp table (not sure which one is required)

so that I can have a list of dates that are on Saturday that fall within a given date range.

This list will be used in a join to associate records with weeks.

What are my options?

Sample Input:

From: 03/01/2013

To: 04/30/2013

Results:

Week Ending - 03/02/2013 - 03/09/2013 - 03/16/2013 - 03/23/2013 - 03/30/2013 - 04/06/2013 - 04/13/2013 - 04/20/2013 - 04/27/2013 - 05/04/2013

Current code: 

create table #TBL7(YEAR  INT, WEEKNUMBER  INT, STARTDATE  DATETIME, ENDDATE DATETIME)


begin
    declare @startdate datetime
                    , @enddate datetime
                    , @ctr int

    SET @startdate = CAST(2013 AS VARCHAR)+ '/01/01'
    SET @enddate = CAST(2013 AS VARCHAR) + '/12/31'
    SET @ctr = 0
    WHILE @enddate >= @startdate
    BEGIN
            SET @ctr = @ctr + 1
            INSERT INTO #TBL7
            values(year(@startdate), @ctr, @startdate, @startdate + 6)
            SET @startdate = @startdate + 7
    END

end



select * from #TBL7

Upvotes: 0

Views: 2170

Answers (3)

Pondlife
Pondlife

Reputation: 16260

First, create a calendar table. Then you have a very simple query:

select [Date] 
from dbo.Calendar 
where DayOfWeek = 'Saturday' and [Date] between '20130301' and '20130430'

A calendar table is almost always the best approach to working with dates because you're working with data, not code, so you can see it's correct and there's no cryptic code to maintain.

Upvotes: 1

Richard Deeming
Richard Deeming

Reputation: 31228

This should work:

WITH cteWeeks (WeekEnding) As
(
   -- Find the Saturday of the first week.
   -- Need to allow for different DATEFIRST settings:
   SELECT
      CASE
         WHEN DatePart(dw, DateAdd(day, @@datefirst, @StartDate)) = 7 THEN @StartDate
         ELSE DateAdd(day, 7 - DatePart(dw, DateAdd(day, @@datefirst, @StartDate)), @StartDate)
      END

   UNION ALL

   SELECT
      DateAdd(day, 7, WeekEnding)
   FROM
      cteWeeks
   WHERE
      WeekEnding < @EndDate
)
SELECT
   WeekEnding
FROM
   cteWeeks
;

http://www.sqlfiddle.com/#!3/d41d8/12095

Upvotes: 0

Art
Art

Reputation: 5792

This is Oracle code. Sorry I do not know how to convert this to SQL SERVER. Should not be very hard. All you need is to use proper functions in place of to_date() and to_char(), and calc the difference between start and end date, e.g. (end_date-start_date)+1:

WITH data(r, some_date) AS 
(
 SELECT 1 r, to_date('03/01/2013', 'MM/DD/YYYY') some_date FROM dual
  UNION ALL
 SELECT r+1, to_date('03/01/2013', 'MM/DD/YYYY')+r FROM data WHERE r < 61 -- (end_date-start_date)+1
)
 SELECT some_date
      , To_Char(some_date, 'DY') wk_day
   FROM data
  WHERE To_Char(some_date, 'DY') = 'SAT'
  /

SOME_DATE    WK_DAY
--------------------
3/2/2013     SAT
3/9/2013     SAT
3/16/2013    SAT
3/23/2013    SAT
3/30/2013    SAT
4/6/2013     SAT
4/13/2013    SAT
4/20/2013    SAT
4/27/2013    SAT

Upvotes: 0

Related Questions