Lynn Pettis (12/4/2012)
Compare:
DECLARE @date DATE = '20121202';
SELECT
CASE WHEN datename(dw,@date) <> 'Sunday' THEN CONVERT(varchar(10), DATEADD(WK, DATEDIFF(WK, 0, @date), 0),
103) ELSE CONVERT(varchar(10), DATEADD(WK, DATEDIFF(WK, 0, @date), - 7), 103) END
go
DECLARE @date DATE = '20121203';
SELECT
CASE WHEN datename(dw,@date) <> 'Sunday' THEN CONVERT(varchar(10), DATEADD(WK, DATEDIFF(WK, 0, @date), 0),
103) ELSE CONVERT(varchar(10), DATEADD(WK, DATEDIFF(WK, 0, @date), - 7), 103) END
go
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
Yup, you have it right. I didn't get to see your post before I posted my answer. I'd sure like to understand a bit more about why that works. It's using the number of weeks since date zero through yesterday, then re-adding the same number of weeks to date zero, which suggests that perhaps the week is defined based on Monday ? Or is it Sunday, and thus why yesterday is used instead of today?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)