I have a requirement to generate data for reporting for the previous week, where the week starts on a Monday and ends on a Sunday.The requirement being that Monday begins at midnight and Sunday ends milliseconds before midnight.
DECLARE @weekstart DATETIME, @weekend DATETIME
-- Set the start of the week as a Monday
SET DATEFIRST 1
@weekstart = DATEADD(wk, DATEDIFF(wk, 0, GETDATE()) - 1, 0),
@weekend = DATEADD(ms, -3,DATEADD(wk,DATEDIFF(wk,7,GETDATE()),7))
I hope that helps!