I am a novice at this but this has to be something simple. I am writing a bit of date-related code:
DECLARE @StartDate AS date
DECLARE @EndDate AS date
CASE
WHEN DATEPART(dw,GETDATE()) = 2 THEN
SET @StartDate = DATEADD(day,-7,GETDATE())
SET @EndDate = DATEADD(day,-1,GETDATE())
WHEN DATEPART(dw,GETDATE()) = 3 THEN
SET @StartDate = DATEADD(day,-8,GETDATE())
SET @EndDate = DATEADD(day,-2,GETDATE())
WHEN DATEPART(dw,GETDATE()) = 4 THEN
SET @StartDate = DATEADD(day,-9,GETDATE())
SET @EndDate = DATEADD(day,-3,GETDATE())
WHEN DATEPART(dw,GETDATE()) = 5 THEN
SET @StartDate = DATEADD(day,-10,GETDATE())
SET @EndDate = DATEADD(day,-4,GETDATE())
WHEN DATEPART(dw,GETDATE()) = 6 THEN
SET @StartDate = DATEADD(day,-11,GETDATE())
SET @EndDate = DATEADD(day,-5,GETDATE())
WHEN DATEPART(dw,GETDATE()) = 7 THEN
SET @StartDate = DATEADD(day,-12,GETDATE())
SET @EndDate = DATEADD(day,-6,GETDATE())
WHEN DATEPART(dw,GETDATE()) = 1 THEN
SET @StartDate = DATEADD(day,-13,GETDATE())
SET @EndDate = DATEADD(day,-7,GETDATE())
END;
PRINT @StartDate
PRINT @EndDate
However, I get the following error messages:
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'CASE'.
Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'WHEN'.
Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'WHEN'.
Msg 156, Level 15, State 1, Line 13
Incorrect syntax near the keyword 'WHEN'.
Msg 156, Level 15, State 1, Line 16
Incorrect syntax near the keyword 'WHEN'.
Msg 156, Level 15, State 1, Line 19
Incorrect syntax near the keyword 'WHEN'.
Msg 156, Level 15, State 1, Line 22
Incorrect syntax near the keyword 'WHEN'.
Msg 137, Level 15, State 1, Line 24
Must declare the scalar variable "@EndDate".
Msg 137, Level 15, State 2, Line 27
Must declare the scalar variable "@EndDate".
Any ideas?
For reference, I am using SQL Server 2012.