End Of Week Function

This script creates a scalar function, F_END_OF_WEEK, that returns the end of week date for a passed date and a passed start day of week.

Parameter @DATE can be any valid datetime. Parameter @WEEK_START_DAY must be an integer in the range 1 through 7, with Sun = 1, Mon = 2, Tue = 3, Wed = 4, Thu = 5, Fri = 6, and Sat = 7.

This function is a companion to function F_START_OF_WEEK and has the same input parameters, @DATE and @WEEK_START_DAY. If they are called with the same input parameters, they will return the first and last day of the week.

Function F_END_OF_WEEK will return a null if the end of week day would be later than 9999-12-31.

The test code at the end of the script uses function F_START_OF_WEEK which can be found on this link:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47307

The test code at the end of the script uses function F_TABLE_NUMBER_RANGE which can be found on this link:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685


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






/*
Function created by this script:
dbo.F_END_OF_WEEK( @DATE, @WEEK_START_DAY )
*/
if objectproperty(object_id('dbo.F_END_OF_WEEK'),'IsScalarFunction') = 1
begin drop function dbo.F_END_OF_WEEK end
go
create function dbo.F_END_OF_WEEK
(
@DATEdatetime,
-- Sun = 1, Mon = 2, Tue = 3, Wed = 4
-- Thu = 5, Fri = 6, Sat = 7
-- Default to Sunday
@WEEK_START_DAYint= 1
)
/*
Function: F_END_OF_WEEK
Finds start of last day of week at 00:00:00.000
for input datetime, @DAY, for a week that started
on the day of week of @WEEK_START_DAY.
Returns a null if the end of week date would
be after 9999-12-31.
*/
returnsdatetime
as
begin
declare @END_OF_WEEK_DATEdatetime
declare @FIRST_BOWdatetime
declare @LAST_EOWdatetime

-- Check for valid day of week, and return null if invalid
if not @WEEK_START_DAY between 1 and 7 return null

-- Find the last end of week for the passed day of week
select @LAST_EOW =convert(datetime,2958457+((@WEEK_START_DAY+6)%7))

-- Return null if end of week for date passed is after 9999-12-31
if @DATE > @LAST_EOW return null

-- Find the first valid beginning of week for the date passed.
select @FIRST_BOW = convert(datetime,-53690+((@WEEK_START_DAY+5)%7))

-- If date is before the first beginning of week for the passed day of week
-- return the day before the first beginning of week
if @DATE < @FIRST_BOW
begin
set @END_OF_WEEK_DATE = dateadd(dd,-1,@FIRST_BOW)
return @END_OF_WEEK_DATE
end

-- Find end of week for the normal case as 6 days after the beginning of week
select @END_OF_WEEK_DATE =
dateadd(dd,((datediff(dd,@FIRST_BOW,@DATE)/7)*7)+6,@FIRST_BOW)

return @END_OF_WEEK_DATE

end
go




/*
Start of test script
*/

select
[DATE] = convert(varchar(10),a.DT,121),
--WEEK_START_DAY = convert(varchar(2),b.number),
FUNC_DW =
case b.number
when 1 then 'Sun'
when 2 then 'Mon'
when 3 then 'Tue'
when 4 then 'Wed'
when 5 then 'Thu'
when 6 then 'Fri'
when 7 then 'Sat'
else null
end,
START_OF_WEEK =
convert(varchar(10),dbo.F_START_OF_WEEK( a.DT, b.number ),121),
START_DW =
left(datename(dw,dbo.F_START_OF_WEEK( a.DT, b.number )),3),
END_OF_WEEK =
convert(varchar(10),dbo.F_END_OF_WEEK( a.DT, b.number ),121),
EOW_DW =
left(datename(dw,dbo.F_END_OF_WEEK( a.DT, b.number )),3)
from
(
-- Get dates from end of datetime range
select
DT = dateadd(dd,a1.number,'9999-12-25')
from
dbo.F_TABLE_NUMBER_RANGE(0,6) a1
union all
-- Get some normal dates, +/- 10 day from current date
select
DT = dateadd(dd,a3.number,getdate())
from
dbo.F_TABLE_NUMBER_RANGE(-10,10) a3
union all
-- Get dates from beginning of datetime range
select
DT = dateadd(dd,a2.number,'17530101')
from
dbo.F_TABLE_NUMBER_RANGE(0,6) a2
) a
cross join
dbo.F_TABLE_NUMBER_RANGE(1,7) b
order by
a.dt,
b.number

/*
End of test script
*/









CODO ERGO SUM