Hi Andreas,

There's no such setting in T-SQL. You can adjust week number by -1 if the January, 1st isn't Monday. Something like

DECLARE @dt datetime, @week int, @dtJan01 datetime

SET DATEFIRST 1
--SET @dt = getdate()
SET @dt = '2001/01/01'

-- January 1st 
SET @dtJan01 = DATEADD(yyyy,DATEDIFF(yyyy,0,@Dt),0)
SET @week = DATEPART ( wk, @dt ) - 
	-- Substract 1 if January 1st isn't Monday
	CASE WHEN DATEPART(dw, @dtJan01) = 1 THEN 0 ELSE 1 END
IF @week < 1 BEGIN
	-- If @week < 1 than this week belongs to the last week of the previous year
	-- Calculate the last day of the previous year
	SET @dt = (@dtJan01 - 1)
	-- Recalculate January 1st for the previous year
	SET @dtJan01 = DATEADD(yyyy,DATEDIFF(yyyy,0, @dt),0)
	-- Calculate the last week of the previous year
	SET @week = DATEPART ( wk, @dt ) - 
		-- Substract 1 if January 1st isn't Monday
		CASE WHEN DATEPART(dw, @dtJan01) = 1 THEN 0 ELSE 1 END

END
SELECT @week

Keep in mind that it's a sample code that wasn't tested well.


>we are using SQL-Server 2000 with Visual Foxpro. Is there any equivalent function for VFP-Function:
>
>WEEK(dExpression | tExpression [, nFirstWeek] [, nFirstDayOfWeek])
>
>in SQL-Server?
>
>I know datepart(week,date)-Function, I can set datefirst to 1 (monday for german standard), but I can't set any equivalent like nFirstWeek in VFP (I want that the first week has seven days, so 3 in VFP).
>