How to get last week date range based on current date in sql?

I generated some spaced out dates in the parms CTE then SELECT the CurrentDate from parms, the Sunday of the week prior to CurrentDate and the Saturday of the week prior to CurrentDate. I'm assuming that you want the dtate range to be Sunday - Saturday.

Sunday - Saturday Ranges

;WITH parms (CurrentDate) AS (    SELECT DATEADD(dd, -14, CURRENT_TIMESTAMP) UNION     SELECT DATEADD(dd, -6, CURRENT_TIMESTAMP) UNION    SELECT DATEADD(dd, 2, CURRENT_TIMESTAMP) UNION    SELECT DATEADD(dd, 8, CURRENT_TIMESTAMP) UNION    SELECT DATEADD(dd, 15, CURRENT_TIMESTAMP) UNION    SELECT DATEADD(dd, 20, CURRENT_TIMESTAMP))SELECT CurrentDate     , LastWeekSunday   = DATEADD(dd, -1, DATEADD(ww, DATEDIFF(ww, 0, CurrentDate) - 1, 0))     , LastWeekSaturday = DATEADD(dd,  5, DATEADD(ww, DATEDIFF(ww, 0, CurrentDate) - 1, 0))FROM parms

Monday to Sunday Ranges

;WITH parms (CurrentDate) AS (    SELECT DATEADD(dd, -14, CURRENT_TIMESTAMP) UNION     SELECT DATEADD(dd, -6, CURRENT_TIMESTAMP) UNION    SELECT DATEADD(dd, 2, CURRENT_TIMESTAMP) UNION    SELECT DATEADD(dd, 8, CURRENT_TIMESTAMP) UNION    SELECT DATEADD(dd, 15, CURRENT_TIMESTAMP) UNION    SELECT DATEADD(dd, 20, CURRENT_TIMESTAMP))SELECT CurrentDate     , LastWeekMonday   = DATEADD(dd,  0, DATEADD(ww, DATEDIFF(ww, 0, DATEADD(dd, -1, CurrentDate)) - 1, 0))     , LastWeekSunday   = DATEADD(dd,  6, DATEADD(ww, DATEDIFF(ww, 0, DATEADD(dd, -1, CurrentDate)) - 1, 0))FROM parms

If you just want the prior week's Monday to the prior week's Sunday from today rather than from a column of dates you can use this

SELECT CURRENT_TIMESTAMP     , LastWeekSunday   = DATEADD(dd,  0, DATEADD(ww, DATEDIFF(ww, 0, DATEADD(dd, -1, CURRENT_TIMESTAMP)) - 1, 0))     , LastWeekSaturday = DATEADD(dd,  6, DATEADD(ww, DATEDIFF(ww, 0, DATEADD(dd, -1, CURRENT_TIMESTAMP)) - 1, 0))

This solution is tested and works. I am getting the previous week's Monday and Sunday as upper and lower bounds.

SELECT    -- 17530101 or 1753-01-01 is the minimum date in SQL Server    DATEADD(dd, ((DATEDIFF(dd, '17530101', GETDATE()) / 7) * 7) - 7, '17530101') AS [LowerLimit], -- Last Week's Monday    DATEADD(dd, ((DATEDIFF(dd, '17530101', GETDATE()) / 7) * 7) - 1, '17530101') AS [UpperLimit] -- Last Week's Sunday.

Which can be used like this in a real world query:

SELECT    *FROM    SomeTableWHERE    SomeTable.[Date] >= DATEADD(dd, ((DATEDIFF(dd, '17530101', GETDATE()) / 7) * 7) - 7, '17530101') AND    SomeTable.[Date] <= DATEADD(dd, ((DATEDIFF(dd, '17530101', GETDATE()) / 7) * 7) - 1, '17530101')

Here are some tests:

1. Leap Year

Current Date: 2016-02-29 00:00:00.000

Results:

LowerLimit                 UpperLimit2016-02-22 00:00:00.000    2016-02-28 00:00:00.000

2. Last Week was in different year

Current Date: 2016-01-06 00:00:00.000

LowerLimit                 UpperLimit2015-12-28 00:00:00.000    2016-01-03 00:00:00.000

3. Lower limit in previous month and upper limit in current month

Current Date: 2016-05-04 00:00:00.000

LowerLimit                 UpperLimit2016-04-25 00:00:00.000    2016-05-01 00:00:00.000

4. Current Date is Sunday

Current Date: 2016-05-08 00:00:00.000

LowerLimit                 UpperLimit2016-04-25 00:00:00.000    2016-05-01 00:00:00.000

Bill's code worked well, but I needed to make a slight edit if I wanted to get last Sunday to Saturday based on today's date.

select CURRENT_TIMESTAMP, LastWeekSunday   = DATEADD(dd,  -1, DATEADD(ww, DATEDIFF(ww, 0, DATEADD(dd, -1, CURRENT_TIMESTAMP)) - (case datename(weekday,CURRENT_TIMESTAMP) when 'Sunday' then 0 else 1 end), 0)), LastWeekSaturday = DATEADD(dd,  5, DATEADD(ww, DATEDIFF(ww, 0, DATEADD(dd, -1, CURRENT_TIMESTAMP)) - (case datename(weekday,CURRENT_TIMESTAMP) when 'Sunday' then 0 else 1 end), 0))

Rgds - Matt