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

Question

I need the correct syntax to give me :

1. Previous week's Monday's date based on the current date/time using GETDATE()
2. Previous week's Sunday's date based on the current date/time using GETDATE()

So, based on today's date (14/09/2012) I would want the following:

1. Previous Monday's date = 03/09/2012
2. Previous Sunday's date = 09/09/2012

Answer 1

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)