help translate this week query from Oracle PL/SQL to SQL Server 2008
- by Sarah Vessels
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
...
