SQL SERVER DATEPART week problem [Solved]

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

wot do you mean not get the number you want?

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.

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. :D

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

Code:

---

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

---