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
 Start of Week Function, Part Deux
 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 - 01/06/2006 :  19:02:41  Show Profile  Reply with Quote
Another variation of start of week function, adapted from this function:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47307

This returns a start of week for a given date and start day of week, offset +/- by a number of weeks.

See the code below for samples of how to call it, and sample results.


drop function dbo.F_START_OF_WEEK_INCREMENT
go
create function dbo.F_START_OF_WEEK_INCREMENT
(
	@DATE			datetime,
	@WEEK_START_DAY	varchar(10)	= 'sunday',
	@WEEK_INCREMENT	int		= 0 
)
/*
Find the fisrt date on or before @DATE + @WEEK_INCREMENT
that matches day of week of @WEEK_START_DAY.
*/
returns		datetime
as
begin

select @DATE = dateadd(day,@WEEK_INCREMENT*7,@DATE)

declare @WEEK_START_DAY_NUM int

select @WEEK_START_DAY_NUM =
	case lower(@WEEK_START_DAY)
	when 'sunday'		then 1
	when 'monday'		then 2
	when 'tuesday'		then 3
	when 'wednesday'	then 4
	when 'thursday'	then 5
	when 'friday'		then 6
	when 'saturday'	then 7
	end

declare	 @START_OF_WEEK_DATE	datetime
declare	 @FIRST_BOW			datetime

-- Check for valid day of week
if @WEEK_START_DAY_NUM between 1 and 7
	begin
	-- Find first day on or after 1753/1/1 (-53690)
	-- matching day of week of @WEEK_START_DAY_NUM
	-- 1753/1/1 is earliest possible SQL Server date.
	select @FIRST_BOW = convert(datetime,-53690+((@WEEK_START_DAY_NUM+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
set nocount on
declare @DATE			datetime
declare @WEEK_INCREMENT	int

select @DATE			= '2006/1/7'
select @WEEK_INCREMENT	= 1

select  [Def]=dbo.F_START_OF_WEEK_INCREMENT(@DATE,default,default)
select  [Sun]=dbo.F_START_OF_WEEK_INCREMENT(@DATE,'sunday',@WEEK_INCREMENT)
select  [Mon]=dbo.F_START_OF_WEEK_INCREMENT(@DATE,'monday',@WEEK_INCREMENT)
select  [Tue]=dbo.F_START_OF_WEEK_INCREMENT(@DATE,'tuesday',@WEEK_INCREMENT)
select  [Wed]=dbo.F_START_OF_WEEK_INCREMENT(@DATE,'wednesday',@WEEK_INCREMENT)
select  [Thu]=dbo.F_START_OF_WEEK_INCREMENT(@DATE,'thursday',@WEEK_INCREMENT)
select  [Fri]=dbo.F_START_OF_WEEK_INCREMENT(@DATE,'friday',@WEEK_INCREMENT)
select  [Sat]=dbo.F_START_OF_WEEK_INCREMENT(@DATE,'saturday',@WEEK_INCREMENT)


Results:

Def
------------------------
2006-01-01 00:00:00.000

Sun
------------------------
2006-01-08 00:00:00.000

Mon
------------------------
2006-01-09 00:00:00.000

Tue
------------------------
2006-01-10 00:00:00.000

Wed
------------------------
2006-01-11 00:00:00.000

Thu
------------------------
2006-01-12 00:00:00.000

Fri
------------------------
2006-01-13 00:00:00.000

Sat
------------------------
2006-01-14 00:00:00.000







CODO ERGO SUM
  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.03 seconds. Powered By: Snitz Forums 2000