Start Of Week Function

I wrote the following function to find the start of week date for a given date and a given start day of week.

For example:
If the day passed is Saturday, 2005-03-19, and Sunday is the start of the week, it returns: 2005-03-13 00:00:00.000

If the day passed is Monday, 2005-03-14, and Sunday is the start of the week, it returns: 2005-03-13 00:00:00.000

If the day passed is Monday, 2005-03-14, and Monday is the start of the week, it returns: 2005-03-14 00:00:00.000


Does anyone have a simpler algorithim for start of week that they care to post?


Edit (2006/4/15):
Posted a companion function F_END_OF_WEEK, on this topic:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64760


There are other Start of Time Period Functions posted here:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64755


There are other End Date of Time Period Functions here:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64759





create function dbo.F_START_OF_WEEK
(
@DATEdatetime,
-- Sun = 1, Mon = 2, Tue = 3, Wed = 4
-- Thu = 5, Fri = 6, Sat = 7
-- Default to Sunday
@WEEK_START_DAYint= 1
)
/*
Find the fisrt date on or before @DATE that matches
day of week of @WEEK_START_DAY.
*/
returnsdatetime
as
begin
declare @START_OF_WEEK_DATEdatetime
declare @FIRST_BOWdatetime

-- Check for valid day of week
if @WEEK_START_DAY between 1 and 7
begin
-- Find first day on or after 1753/1/1 (-53690)
-- matching day of week of @WEEK_START_DAY
-- 1753/1/1 is earliest possible SQL Server date.
select @FIRST_BOW = convert(datetime,-53690+((@WEEK_START_DAY+5)%7))
-- Verify beginning of week not before 1753/1/1
if @DATE >= @FIRST_BOW
begin
select @START_OF_WEEK_DATE =
dateadd(dd,(datediff(dd,@FIRST_BOW,@DATE)/7)*7,@FIRST_BOW)
end
end

return @START_OF_WEEK_DATE

end
go

-- Sample function calls

select dbo.F_START_OF_WEEK(getdate(),default) -- Returns Date for Sunday

select dbo.F_START_OF_WEEK(getdate(),1)-- Returns Date for Sunday
select dbo.F_START_OF_WEEK(getdate(),2)-- Returns Date for Monday
select dbo.F_START_OF_WEEK(getdate(),3)-- Returns Date for Tuesday
select dbo.F_START_OF_WEEK(getdate(),4)-- Returns Date for Wednesday
select dbo.F_START_OF_WEEK(getdate(),5)-- Returns Date for Thursday
select dbo.F_START_OF_WEEK(getdate(),6)-- Returns Date for Friday
select dbo.F_START_OF_WEEK(getdate(),7)-- Returns Date for Saturday

select dbo.F_START_OF_WEEK(getdate(),0)-- Returns NULL
select dbo.F_START_OF_WEEK(getdate(),8)-- Returns NULL



Edited 2005/4/9:

I thought I would also post an alternate way of doing the Start of Week instead of using the F_START_OF_WEEK function. These queries demo doing the Start of Week inline in a query, and use a similar algorithm to find the start of week, but the start day of week is hard coded.

I posted two versions. The first version is simpler, but it has a minor flaw that returns a false result if the start of week would be before 1753/1/1. For the vast majority of applications this would not be a problem. In the second, the algorithm is modified slightly to cause it to overflow if you pick a date that would result in a start of week before 1753/1/1. Note that the F_START_OF_WEEK function returns a NULL in this situation.

The demo queries use the F_TABLE_NUMBER_RANGE that is posted in another thread in order to generate dates to demonstrated the results:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685



-- First demo query for Start of Week
-- Returns bad result if the start of week would be before 1753/1/1
select
DATE,
Sun = dateadd(dd,(datediff(dd,-53684,a.DATE)/7)*7,-53684),
Mon = dateadd(dd,(datediff(dd,-53690,a.DATE)/7)*7,-53690),
Tue = dateadd(dd,(datediff(dd,-53689,a.DATE)/7)*7,-53689),
Wed = dateadd(dd,(datediff(dd,-53688,a.DATE)/7)*7,-53688),
Thu = dateadd(dd,(datediff(dd,-53687,a.DATE)/7)*7,-53687),
Fri = dateadd(dd,(datediff(dd,-53686,a.DATE)/7)*7,-53686),
Sat = dateadd(dd,(datediff(dd,-53685,a.DATE)/7)*7,-53685)
from
(
select
DATE = convert(datetime,number)
from
F_TABLE_NUMBER_RANGE(36524,40000)
) a


-- Second demo query for Start of Week
-- Modified to cause an error instead of returning a bad date
-- if the start of week would be before 1753/1/1
select
DATE,
Sun = dateadd(dd,((datediff(dd,-53684,a.DATE+7)/7)*7)-7,-53684),
Mon = dateadd(dd,((datediff(dd,-53690,a.DATE+7)/7)*7)-7,-53690),
Tue = dateadd(dd,((datediff(dd,-53689,a.DATE+7)/7)*7)-7,-53689),
Wed = dateadd(dd,((datediff(dd,-53688,a.DATE+7)/7)*7)-7,-53688),
Thu = dateadd(dd,((datediff(dd,-53687,a.DATE+7)/7)*7)-7,-53687),
Fri = dateadd(dd,((datediff(dd,-53686,a.DATE+7)/7)*7)-7,-53686),
Sat = dateadd(dd,((datediff(dd,-53685,a.DATE+7)/7)*7)-7,-53685)
from
(
select
DATE = convert(datetime,number)
from
F_TABLE_NUMBER_RANGE(36524,40000)
) a






CODO ERGO SUM