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. 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
...