help translate this week query from Oracle PL/SQL to SQL Server 2008

Question

I have the following query that runs in my Oracle database and I want to have the equivalent for a SQL Server 2008 database:

SELECT TRUNC( /* Midnight Sunday */
         NEXT_DAY(SYSDATE, 'SUN') - (7*LEVEL)
       ) AS week_start,
       TRUNC( /* 23:59:59 Saturday */
         NEXT_DAY(NEXT_DAY(SYSDATE, 'SUN') - (7*LEVEL), 'SAT') + 1
       ) - (1/(60*24)) + (59/(60*60*24)) AS week_end
FROM DUAL
CONNECT BY LEVEL <= 4 /* Get the past 4 weeks */

What the query does is get the start of the week and the end of the week for the last 4 weeks. The number of weeks is arbitrary and should be easily modified in the SQL Server query I want. It generates data like the following:

WEEK_START          WEEK_END
2010-03-07 00:00:00 2010-03-13 23:59:59
2010-02-28 00:00:00 2010-03-06 23:59:59
...

The part I'm currently stuck on translating is CONNECT BY LEVEL since it seems SQL Server 2008 doesn't have an equivalent. I would prefer to simply adjust a line like CONNECT BY LEVEL <= 4 and have the query generate more or fewer weeks (i.e., I don't want to have to adjust multiple UNION ALL statements).

Edit: here's what I have so far that gets the beginning and end of the current week:

   SELECT week_start,
          DATEADD(SECOND, -1, DATEADD(DAY, 7, week_start)) AS week_end
   FROM (
          SELECT CAST(
                   CONVERT(
                     VARCHAR(10),
                     DATEADD(DAY, 1-DATEPART(DW, GETDATE()), GETDATE()),
                     111
                   ) AS DATETIME
                 ) AS week_start
        ) AS week_start_view

I don't mind if the query shows the current week start and end date or if it starts at the previous week.

Answer 1

I modified OMG Ponies' answer because it looked like a good idea, it just had the wrong week_end value on each week and also showed future weeks instead of past weeks. I came up with the following:

WITH dates AS (
  SELECT DATEADD(
           DD, 
           1 - DATEPART(DW, CONVERT(VARCHAR(10), starting_date, 111)), 
           CONVERT(VARCHAR(10), starting_date, 111)
         ) AS midnight
  FROM (
         SELECT DATEADD(WEEK, -3, GETDATE()) AS starting_date
       ) AS starting_date_view

  UNION ALL

  SELECT DATEADD(DD, 7, midnight)
  FROM dates
  WHERE DATEADD(DD, 7, midnight) < GETDATE()
) SELECT midnight AS week_start,
         DATEADD(SS, -1, DATEADD(DAY, 7, midnight)) AS week_end
  FROM dates

It generates the past 4 weeks:

week_start                 week_end
2010-02-14 00:00:00.000    2010-02-20 23:59:59.000
2010-02-21 00:00:00.000    2010-02-27 23:59:59.000
2010-02-28 00:00:00.000    2010-03-06 23:59:59.000
2010-03-07 00:00:00.000    2010-03-13 23:59:59.000

This is better than my previous answer, I think, because it does not rely on another table having a particular number of rows. The number of weeks generated can be changed by altering only one digit: the 3 in SELECT DATEADD(WEEK, -3, GETDATE()) AS starting_date. The current week is included, and that digit represents how many additional weeks prior to the current week should be shown.

Iterate over Past Weeks up to Now, Excluding Current Week

Update: and here's a version that excludes the current week:

WITH dates AS (
  SELECT DATEADD(
           DD,
           1 - DATEPART(DW, CONVERT(VARCHAR(10), starting_date, 111)), 
           CONVERT(VARCHAR(10), starting_date, 111)
         ) AS midnight_sunday
  FROM (
         SELECT DATEADD(WEEK, -4, GETDATE()) AS starting_date
       ) AS starting_date_view

  UNION ALL

  SELECT DATEADD(DD, 7, midnight_sunday)
  FROM dates
  WHERE DATEADD(DD, 7, midnight_sunday) <
        DATEADD(
          DD,
          1 - DATEPART(DW, CONVERT(VARCHAR(10), GETDATE(), 111)), 
          CONVERT(VARCHAR(10), GETDATE(), 111)
        )
) SELECT midnight_sunday AS week_start,
         DATEADD(SS, -1, DATEADD(DAY, 7, midnight_sunday)) AS week_end
  FROM dates

Its results:

week_start                 week_end
2010-02-07 00:00:00.000    2010-02-13 23:59:59.000
2010-02-14 00:00:00.000    2010-02-20 23:59:59.000
2010-02-21 00:00:00.000    2010-02-27 23:59:59.000
2010-02-28 00:00:00.000    2010-03-06 23:59:59.000

Iterate over Past Months up to Now

I later found a need for a monthly version of this query. Here is that modification:

WITH dates AS (
  SELECT CAST(
        FLOOR(CAST(starting_date AS DECIMAL(12, 5))) -
        (DAY(starting_date) - 1) AS DATETIME
      ) AS month_start
  FROM (
         SELECT DATEADD(MONTH, -3, GETDATE()) AS starting_date
       ) AS starting_date_view

  UNION ALL

  SELECT DATEADD(MONTH, 1, month_start)
  FROM dates
  WHERE DATEADD(MONTH, 1, month_start) < GETDATE()
) SELECT month_start,
         DATEADD(SS, -1, DATEADD(MONTH, 1, month_start)) AS month_end
  FROM dates
  ORDER BY month_start DESC

Answer 2

Use (but don't forget to vote for Sarah):

WITH dates AS (
    SELECT DATEADD(DD, 
                   1 - DATEPART(DW, CONVERT(VARCHAR(10), starting_date, 111)), 
                   CONVERT(VARCHAR(10), starting_date, 111)
                   ) AS midnight
      FROM (SELECT DATEADD(WEEK, -3, GETDATE()) AS starting_date) AS starting_date_view
    UNION ALL
    SELECT DATEADD(DD, 7, midnight)
      FROM dates
     WHERE DATEADD(DD, 7, midnight) < GETDATE()) 
SELECT midnight AS week_start,
       DATEADD(SS, -1, DATEADD(DAY, 7, midnight)) AS week_end
  FROM dates

Previously:

1. Set the first day of the week to be Sunday with the SET DATEFIRST command:

   SET DATEFIRST 7
   

2. The SQL Server 2005+ equivalent to Oracle's CONNECT BY LEVEL is the recursive CTE (ANSI standard btw). Use:

   WITH dates AS (
      SELECT DATEADD(DD, 
                     1 - DATEPART(DW, CONVERT(VARCHAR(10), GETDATE(), 111)), 
                     CONVERT(VARCHAR(10), GETDATE(), 111)) AS date
      UNION ALL
      SELECT DATEADD(dd, 7, d.date)
        FROM dates d
       WHERE DATEADD(dd, 7, d.date) <= DATEADD(dd, 4*7, GETDATE()))
   SELECT t.date AS week_start,
          DATEADD(ss, -1, DATEADD(DAY, 7, t.date)) AS week_end
     FROM dates t
   

See this link for explaining how to get the first day of the week. To alter the number of weeks, change the DATEADD(dd, 4*7, GETDATE()), where 4 represents the number of weeks you want generated.