4

I have a date range where a start date is 2 years prior to today. e.g.) '05/29/2007' ~ '05/29/2009'.

How can I break above date range so that I would get a list like the following?

(start date starts with "05/27/2007", not "05/29/2007" since the start of weekday is Sunday and '05/27/2007' is the first day of week for '05/29/2007' and the same reasoning for the last EndDate, 05/30/2009, which is Saturday)

StartDate   EndDate
05/27/2007  06/02/2007
06/03/2007  06/09/2007
...
05/24/2009  05/30/2009

[UPDATE] here is my final query

WITH hier(num, lvl) AS (
    SELECT  0, 1
        UNION ALL
    SELECT  100, 1
        UNION ALL 
    SELECT  num + 1, lvl + 1
    FROM    hier
    WHERE   lvl < 100
)
SELECT  num, lvl,
    DATEADD(dw, -DATEPART(dw, '2007-05-29'), '2007-05-29') + num  * 7,
    DATEADD(dw, -DATEPART(dw, '2007-05-29'), '2007-05-29') + (num + 1) * 7
FROM    hier
where   num <= 104  --; 52 weeks/year * 2
ORDER BY num
dance2die
  • 35,807
  • 39
  • 131
  • 194
  • Thank you for posting the update with your final result. Most people don't realize how helpful it can be for others. – Crimius Aug 16 '12 at 12:44

3 Answers3

4
WITH hier(num, lvl) AS (
        SELECT  0, 1
        UNION ALL
        SELECT  100, 1
        UNION ALL 
        SELECT  num + 1, lvl + 1
        FROM    hier
        WHERE   lvl < 100
        )
SELECT  DATEADD(dw, -DATEPART(dw, '29.05.2007'), '29.05.2007') + num  * 7,
        DATEADD(dw, -DATEPART(dw, '29.05.2007'), '29.05.2007') + (num + 1) * 7
FROM    hier
WHERE   DATEADD(dw, -DATEPART(dw, '29.05.2007'), '29.05.2007') + num * 7 < '29.05.2009' 
ORDER BY
        num

This will generate a rowset with the ranges you need.

Quassnoi
  • 413,100
  • 91
  • 616
  • 614
2

You need to make sure that @@DATEFIRST is properly sent, then you can simply use the code below. Read up on DATEFIRST though so that you understand it fully.

SET DATEFIRST 1

DECLARE @my_date DATETIME

SET @my_date = '2007-05-29'

SELECT
     DATEADD(dw, -DATEPART(dw, @my_date), @my_date) AS StartDate,
     DATEADD(dw, 6 - DATEPART(dw, @my_date), @my_date) AS EndDate
Tom H
  • 46,766
  • 14
  • 87
  • 128
  • I am trying to figure out what "variables" I need to extract out of your query. Let me see... – dance2die May 27 '09 at 16:03
  • I used @my_date just to make it run without a table. That would be the column or variable that you have as 5/29/2007. If you're basing it off of the current date then just set a variable to that and use it. Looking at Quassnoi's it looks like maybe you want a set of date ranges instead of a single date range, so this might not fully answer your question. You can join to a numbers table or a calendar table to expand it if needed or a CTE as Quassnoi has done. – Tom H May 27 '09 at 16:21
0

Should be pretty simple directy in your SQL statement... I've had a long history of dealing with dates and date arithmetic, so I would approach something like:

select 
      datepart( year, YourDateField ) as GroupYear,
      datepart( week, ( YourDateField - datepart( day, YourDateField ) +1 ) as GroupWeek,
      YourDateField,
      OtherFields
  from 
      YourTable
  where 
      whateverDateRange...
  group by 
     GroupYear,
     GroupWeek

The reason for the year and week is if you span multiple years, you would have week 1 of both years before week 2 of first year, etc..

Now, how the math works... for the GroupWeek. This will actually compute the first day of the week based on whatever date your data has as the basis... Say you have data for May 25, 26, 27 this year... They would respectively be the 2nd, 3rd and 4th day of the week -- being Sunday starts the week at day 1. So:

May 25 - 2 (day of week) = May 23 (Saturday) +1 = May 24 (Sunday of the week). May 26 - 3 (day of week) = May 23 ... etc May 27 - 4 (day of week) = May 23

So, by also including the original date field in question, you can see the real date too all in one SQL call...

DRapp
  • 47,638
  • 12
  • 72
  • 142