sgmunson (12/4/2012)
How about:
CASE
WHEN DATEPART(dw, THE_DATE) = 1 THEN DATEADD(dd, -6, THE_DATE)
ELSE DATEADD(dd, 0 - (DATEPART(dw, THE_DATE) - 2), THE_DATE)
END
where THE_DATE is assumed to be the date field...
No conditional logic required:
DECLARE @date DATE = '20121202';
select dateadd(wk, datediff(wk, 0, DATEADD(dd,-1,@Date)), 0);
GO
DECLARE @date DATE = '20121203';
select dateadd(wk, datediff(wk, 0, DATEADD(dd,-1,@Date)), 0);
GO