Previous Monday & previous Sunday's date based on today's date

Easy:

--start of last week
SELECT DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 0)

--end of last week
SELECT DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 6)

EDIT:

The below will handle the Sunday date issue.

DECLARE @input varchar(10)
--SET @input = '9/9/2012' -- simulates a Sunday
SET @input = GETDATE()

--start of last week
SELECT DATEADD(wk, DATEDIFF(wk, 6, 
CASE DATEPART(dw,@input)
WHEN 1 THEN DATEADD(d,-1,@input)
ELSE @input
END
), 0)

--end of last week
SELECT DATEADD(wk, DATEDIFF(wk, 6, 
CASE DATEPART(dw,@input)
WHEN 1 THEN DATEADD(d,-1,@input)
ELSE @input
END
), 6)

Instead of using a case option, you could also do this to get the current week's Sunday:

SELECT DATEADD(dd, DATEPART(DW,GETDATE())*-1+1, GETDATE())

To get the previous week's Sunday, subtract 7 more days:

SELECT DATEADD(dd, DATEPART(DW,GETDATE())*-1-6, GETDATE())

Even better, I think, this works for any date, any week day, with any DateFirst parameter (set the first day of the week, generally 1-Monday in France, default is 7-Sunday).

create function [dbo].[previousWeekDayDate](@anyDate date, @anyWeekDay int)
returns Date
as
begin
    return DATEADD(dd, ((DATEPART(dw,@anyDate) + @@DateFirst - @anyWeekDay + 13) % 7) * -1, @anyDate)
end

works for SQL 2008, create the function and use:

SELECT dbo.previousWeekDayDate(GetDate(),1) --for Monday
SELECT dbo.previousWeekDayDate(GetDate(),7) --for Sunday