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

Question

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

Can you show sample data and desired results? Word problems aren't very effective here. — Aaron Bertrand, Apr 05 '13 at 14:36
Does this help? http://stackoverflow.com/questions/15543977/ms-sql-server-2008-getting-start-date-and-end-date-of-the-week-to-next-8-weeks/15546165#15546165 — Tom Chantler, Apr 05 '13 at 14:43
What is your programming question? You need to come up with your solutio and if you are stuck on some programming this site can help you. — Mowgli, Apr 05 '13 at 15:05

score 1 · Answer 1 · edited Nov 13 '18 at 23:14

1

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.

edited Nov 13 '18 at 23:14

Community

1
1

answered Apr 05 '13 at 16:00

Pondlife

15,992
6
37
51

score 0 · Answer 2 · answered Apr 05 '13 at 15:22

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

score 0 · Answer 3 · answered Apr 05 '13 at 15:35

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

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

3 Answers3