Search This Blog

Find the start Day of a Week

Here is a function that returns the startDate of a week

CREATE FUNCTION RetStartOfWeek (
@DATE datetime,
-- Sun = 1, Mon = 2, Tue = 3, Wed = 4 Thu = 5, Fri = 6, Sat = 7 Default to Sunday @StartofWeek int = 1
)
/*Find the fisrt date on or before @DATE that matches day of week of @StartofWeek.*/
RETURNS datetime
AS
BEGIN
DECLARE @StartOfWeekDate datetime
DECLARE @FIRST_BOW datetime
-- Check for valid day of week
IF @StartofWeek between 1 and 7
BEGIN
-- Find first day on or after 1753/1/1 (-53690)
-- matching day of week of @StartofWeek
-- 1753/1/1 is earliest possible SQL Server date.
SELECT @FIRST_BOW = CONVERT(datetime,-53690+((@StartofWeek+5)%7))
-- Verify beginning of week not before 1753/1/1
IF @DATE >= @FIRST_BOW
BEGIN
SELECT @StartOfWeekDate = DATEADD(dd,(DATEDIFF (dd,@FIRST_BOW,@DATE)/7)*7,@FIRST_BOW)
END
END
RETURN @StartOfWeekDateend

No comments: