CREATE FUNCTION [dbo].[Get_ISOWeekNo](@DATE as Datetime)
RETURNS INT
WITH EXECUTE AS CALLER
AS
------------------------------------------------------------------------------
-- Get_ISOWeekNo
--
-- This function is copied from the SQL Server 2005 help file
-- see: CREATE FUNCTION
--
-- this function will return the week number from entered date based on ISO calculation
-- the ISO calculations are written in ISO 8601, defined in 1988
-- if year has to be added to result we will get result like: 200452 for week 52 in year 2004
-- if year doesn''t have to be added to result we will get result like: 52 for week 52 for any year
-- 
-- This function is called in: Generate_REF_Datum
--
-- CreationDate:	19 december 2006
------------------------------------------------------------------------------
BEGIN
DECLARE @ISOweek INT
DECLARE @YearOfWeek INT

-- get default Microsoft Week Number
SET @YearOfWeek = DATEPART(yy,@DATE)
SET @ISOweek= DATEPART(wk, @DATE)+1-DATEPART(wk, CAST(@YearOfWeek as CHAR(4))+''0104'')

--Special cases: Jan 1-3 may belong to the previous year
IF (@ISOweek=0) 
	BEGIN
	SET @YearOfWeek = @YearOfWeek - 1
	-- Get WeekNumber of 24 December of previous year, and add 1 to get proper result
	-- This is a recursive call.
	SET @ISOweek=
		dbo.Get_ISOWeekNo(CAST(@YearOfWeek AS CHAR(4))
		+ ''12''
		+ CAST(24 + DATEPART(DAY,@DATE) AS CHAR(2))
		)+1
	END

--Special case: Dec 29-31 may belong to the next year
IF ((DATEPART(mm, @DATE)=12) AND ((DATEPART(dd, @DATE)-DATEPART(dw, @DATE))>= 28))
	BEGIN
	SET @YearOfWeek = @YearOfWeek + 1
	SET @ISOweek=1
	END

--Result incl. YearNumber of Week
--RETURN((@YearOfWeek*100)+@ISOweek)

RETURN(@ISOweek)
END