Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 Script Library
 End of Week Function

Author  Topic 

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-04-15 : 20:28:49
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
(
@DATE datetime,
-- Sun = 1, Mon = 2, Tue = 3, Wed = 4
-- Thu = 5, Fri = 6, Sat = 7
-- Default to Sunday
@WEEK_START_DAY int = 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.
*/
returns datetime
as
begin
declare @END_OF_WEEK_DATE datetime
declare @FIRST_BOW datetime
declare @LAST_EOW datetime

-- 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
*/






Edit 2010-11-12:

This code shows an inline code solution for any ending day of week, Sunday through Saturday. It will work for any date within the range of 1753-01-08 through 9999-12-24

select
a.DT,
EOWSun = dateadd(dd,((datediff(dd,-53690,a.DT)/7)*7)+6,-53690),
EOWMon = dateadd(dd,((datediff(dd,-53689,a.DT)/7)*7)+6,-53689),
EOWTue = dateadd(dd,((datediff(dd,-53688,a.DT)/7)*7)+6,-53688),
EOWWed = dateadd(dd,((datediff(dd,-53687,a.DT)/7)*7)+6,-53687),
EOWThu = dateadd(dd,((datediff(dd,-53686,a.DT)/7)*7)+6,-53686),
EOWFri = dateadd(dd,((datediff(dd,-53685,a.DT)/7)*7)+6,-53685),
EOWSat = dateadd(dd,((datediff(dd,-53684,a.DT)/7)*7)+6,-53684)
from
( -- Generate test data dates
select
DT = dateadd(dd,aa.number,'20100101')
from
-- Number Table Function available here
-- http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685
dbo.F_TABLE_NUMBER_RANGE(0,365) aa
) a
order by
a.DT



Alternative inline code using date strings instead of representing dates as numbers:
select
a.DT,
EOWSun = dateadd(dd,((datediff(dd,'17530101',a.DT)/7)*7)+6,'17530101'),
EOWMon = dateadd(dd,((datediff(dd,'17530102',a.DT)/7)*7)+6,'17530102'),
EOWTue = dateadd(dd,((datediff(dd,'17530103',a.DT)/7)*7)+6,'17530103'),
EOWWed = dateadd(dd,((datediff(dd,'17530104',a.DT)/7)*7)+6,'17530104'),
EOWThu = dateadd(dd,((datediff(dd,'17530105',a.DT)/7)*7)+6,'17530105'),
EOWFri = dateadd(dd,((datediff(dd,'17530106',a.DT)/7)*7)+6,'17530106'),
EOWSat = dateadd(dd,((datediff(dd,'17530107',a.DT)/7)*7)+6,'17530107')
from
( -- Generate test data dates
select
DT = dateadd(dd,aa.number,'20100101')
from
-- Number Table Function available here
-- http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685
dbo.F_TABLE_NUMBER_RANGE(0,365) aa
) a
order by
a.DT


Alternative inline code 2 using date strings and eliminating +6 by selecting second date six days later.
select
a.DT,
EOWSun = dateadd(dd,((datediff(dd,'17530101',a.DT)/7)*7),'17530107'),
EOWMon = dateadd(dd,((datediff(dd,'17530102',a.DT)/7)*7),'17530108'),
EOWTue = dateadd(dd,((datediff(dd,'17530103',a.DT)/7)*7),'17530109'),
EOWWed = dateadd(dd,((datediff(dd,'17530104',a.DT)/7)*7),'17530110'),
EOWThu = dateadd(dd,((datediff(dd,'17530105',a.DT)/7)*7),'17530111'),
EOWFri = dateadd(dd,((datediff(dd,'17530106',a.DT)/7)*7),'17530112'),
EOWSat = dateadd(dd,((datediff(dd,'17530107',a.DT)/7)*7),'17530113')
from
( -- Generate test data dates
select
DT = dateadd(dd,aa.number,'20100101')
from
-- Number Table Function available here
-- http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685
dbo.F_TABLE_NUMBER_RANGE(0,365) aa
) a
order by
a.DT


CODO ERGO SUM

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-11-12 : 18:49:08
I know I've said this before, but...

You need to start a blog! The Script Library forum gets very little visibility as compared to blogs. And once you've got notoriety on your blog, your chances of getting an MVP go way up.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page
   

- Advertisement -