SQL SERVER DATEPART week problem [Solved]

[matt3011]

I want to do a search based on the week of a given date field.

I'm using DATEPART(ww,myField) to get that week number but, I don't get the number I want.

SQL SERVER assumes that the first week begins january 1st.

Is it possible to change that and make the first week of year to be the first full week ?

Thanks

[oceanebelle]

wot do you mean not get the number you want?

[matt3011]

I mean, if you assume that first week of year starts on jan 1st, then today (june, 22nd) we are in week 26 (what I get). But if you assume that first week of year is the first full week of january then we are in week 25.

[oceanebelle]

well how about you just calculate the week then?

get the number of days from the ur starting date until ur end date. then divide it with 7.. and check if there are extra days by using modulo.( days%7). if so then that would be days/7 + 1.

[matt3011]

I don't see your point, since you're just proposing a DATEDIFF method. I want the week number in the year.

I made my own function

CREATE FUNCTION [dbo].[fn_week] (@d_date AS datetime)  
RETURNS tinyint AS  
BEGIN 
	DECLARE @n_week tinyint
	SET @n_week=DATEPART(ww,@d_date)
	IF DATEPART(dw,CAST('01/01/'+CAST(YEAR(@d_date) AS char(4)) AS datetime))<>1
	BEGIN
		SET @n_week=@n_week-1
		IF @n_week=0
		BEGIN
			SELECT @n_week=[dbo].[fn_week] (CAST('31/12/'+CAST((YEAR(@d_date)-1) AS char(4)) AS datetime))  
		END
	END
	return @n_week
END