selectDistinct selectDistinct - 7 months ago 40
SQL Question

Previous Monday & Previous Sundays date based on todays date

I need the correct syntax to give me :


  1. Previous weeks Mondays date based on the current date/time using
    GETDATE()

  2. Previous weeks Sundays date based on the current date/time using
    GETDATE()



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


  1. Previous Mondays date = 03/09/2012

  2. Previous Sundays date = 09/09/2012


Answer

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)
Comments