Week Numbers - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

Week Numbers

Author

Topic

powellmj
Starting Member

2 Posts

Posted - 2005-03-30 : 03:50:26

Does anyone now how I can create a SQL function to return a week number for any date with the following guidlines?
-Weeks begin on a Thursday and end on a Wednesday.
-1st January is always in week 1.
-Week 1 for this year would go from 30/12/2004 to 05/01/2005

I have been going round in circles trying to crack this one. I would be very grateful if anyone has any ideas.

andy8979
Starting Member

36 Posts

Posted - 2005-03-30 : 05:48:41

try using the datepart functions of TSQl They are very useful and give you all the required functions like day of the year, day of the week , week of the year, ...

powellmj
Starting Member

2 Posts

Posted - 2005-03-30 : 06:00:08

It works ok using TSQL, but I can't get the same principle to work in a Function - this is because it does not like the SET DATEFIRST command in a function.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-03-30 : 20:32:52

This query below returns your week number. I will leave it up to you to turn it into a function. Note that this query uses two functions that I posted links for below.

select
	a.Date,
	FIRST_DAY_OF_WEEK = dbo.F_START_OF_WEEK(a.Date,5),
	LAST_DAY_OF_WEEK = dateadd(dd,6,dbo.F_START_OF_WEEK(a.Date,5)),
	FIRST_DAY_OF_YEAR = 
		dateadd(yy,datediff(yy,0,dateadd(dd,6,dbo.F_START_OF_WEEK(a.Date,5))),0),
	START_DAY_OF_FIRST_WEEK_OF_YEAR =
	dbo.F_START_OF_WEEK(dateadd(yy,datediff(yy,0,dateadd(dd,6,dbo.F_START_OF_WEEK(a.Date,5))),0),5),
	-- This is the week of the year
	WEEK_OF_YEAR = 
	(datediff(dd,
	dbo.F_START_OF_WEEK(dateadd(yy,datediff(yy,0,dateadd(dd,6,dbo.F_START_OF_WEEK(a.Date,5))),0),5),
	dbo.F_START_OF_WEEK(a.Date,5))/7)+1
from
	(
	select date = convert(datetime,'2004/12/01')+number 
	from dbo.F_TABLE_NUMBER_RANGE(0,400)
	) a

This query uses the F_START_OF_WEEK function in this topic:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47307

I used the number table function, F_TABLE_NUMBER_RANGE, in this topic to generate the dates to test with:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685

CODO ERGO SUM

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-03-31 : 18:42:14

The SET DATEFIRST ?, is what makes things difficult.
Anyway here is
1. a function tht neutralisez the server setting, by "simulating" a SET DATEFIRST 1 setting.
2. a function that utilisez 1, to calculate the weeknr given a certain starting date.

It's kind of unreadable....


/* this is just for testing, adjust the parameter to the fn to choose the starting dow*/
/* monday=1,tuesday=2,wednesday=3,thursday=4,friday=5,saturday=6,sunday=7 */
SELECT
	theDate, DATENAME(dw,theDate)
	,dbo.uspGetWeekNr(theDate,4) AS WeekNbr
FROM
	(
		SELECT DATEADD(dd,number,'20050101') AS theDate -- hardcode sample date
		FROM master..spt_values	WHERE Type = 'P'	-- serves as number table
	) dates


CREATE FUNCTION dbo.uspGetDwMondayBase
(
	@d DATETIME
)
RETURNS TINYINT WITH SCHEMABINDING
AS
/*	Designed to neutralize setting of DATEFIRST
	This will always return dw as SET DATEFIRST 1
	regardless of the server setting */
BEGIN
	DECLARE @i TINYINT
	SET @i = -1 + @@DATEFIRST + CHARINDEX(LTRIM(DATEPART(dw,@d)),'1234567')
	IF @i > 7
		RETURN @i - 7
	RETURN @i
END
GO

CREATE FUNCTION dbo.uspGetWeekNr
(
	@d DATETIME
	,@wstart TINYINT /* monday=1,tuesday=2,wednesday=3,thursday=4,friday=5,saturday=6,sunday=7 */
)
RETURNS TINYINT WITH SCHEMABINDING
AS
/*	Given a date and a number designating the starting day of the week;
	monday=1,tuesday=2,wednesday=3,thursday=4,friday=5,saturday=6,sunday=7
	This will return the weeknr of the date
	Regardless of the servers @@DATEFIRST value */
BEGIN
	IF @wstart/8 > 0 RETURN NULL
	RETURN
	(
		SELECT
		(
			/* offset depending on what day year starts, and which day we want the week to start on */
			dbo.uspGetDwMondayBase( 1 - @wstart + DATEADD(yy,DATEDIFF(yy,0,@d),0) )
			+ DATEPART(dy,@d)	/* # of the day in the year 1,2,3,4,5,,,365,366*/
			+ 5			/* +7(to get 1 iof 0 when /7) -1(we want (7-1)/7) -1(first day is 1, we want 0-based) */
		) / 7 				/* just divide by 7 to get weeknr */
	)
END
GO


--DROP FUNCTION dbo.uspGetWeekNr
--DROP FUNCTION dbo.uspGetDwMondayBase

rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources