SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 End of Week Function
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 04/15/2006 :  20:28:49  Show Profile  Reply with Quote
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

Edited by - Michael Valentine Jones on 11/12/2010 18:42:20

tkizer
Almighty SQL Goddess

USA
36594 Posts

Posted - 11/12/2010 :  18:49:08  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000