title: help translate this week query from Oracle PL/SQL to SQL Server 2008
body: <p>I have the following query that runs in my Oracle database and I want to have the equivalent for a SQL Server 2008 database:</p>
  
  <pre><code>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 &lt;= 4 /* Get the past 4 weeks */
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>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
  ...
  </code></pre>
  
  <p>The part I'm currently stuck on translating is <code>CONNECT BY LEVEL</code> since it seems SQL Server 2008 doesn't have an equivalent.  I would prefer to simply adjust a line like <code>CONNECT BY LEVEL &lt;= 4</code> and have the query generate more or fewer weeks (i.e., I don't want to have to adjust multiple <code>UNION ALL</code> statements).</p>
  
  <p><strong>Edit:</strong> here's what I have so far that gets the beginning and end of the current week:</p>
  
  <pre><code>   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
  </code></pre>
  
  <p>I don't mind if the query shows the current week start and end date or if it starts at the previous week.</p>