Get ISO week & year from date in SQL-Server 2005

fu_get_ISOWeek_SQL2005.sql

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fu_get_ISOWeek_SQL2005]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fu_get_ISOWeek_SQL2005]
GO



-- Return the ISO week of the year regardless of the DATEFIRST session SETting. 
CREATE FUNCTION [dbo].[fu_get_ISOWeek_SQL2005](@dtDate AS datetime) 
	RETURNS nvarchar(7) WITH RETURNS NULL ON NULL INPUT 
AS 
BEGIN 
	DECLARE @intISOWeekdayNumber int  
	DECLARE @dtThisThursday datetime 
	DECLARE @dtFirstOfThisThursdaysYear datetime 
	DECLARE @intISOWeekdayNumberOfFirstOfThisThursdaysYear int 
	DECLARE @dtFirstThursdayOfYear datetime 
	DECLARE @intISOWeekNumber int 
	DECLARE @intYear int 
	
	-- Get the ISO week day number (Monday = 1) for our date. 
	SET @intISOWeekdayNumber = (((DATEPART(dw, @dtDate) - 1) + (@@DATEFIRST - 1)) % 7) + 1 
	
	-- Get the date of the Thursday in this ISO week. 
	SET @dtThisThursday = DATEADD(d, (4 - @intISOWeekdayNumber), @dtDate) 
	
	-- Get the date of the 1st January for 'this Thursdays' year. 
	SET @dtFirstOfThisThursdaysYear = CAST(CAST(YEAR(@dtThisThursday) AS char(4)) + '-01-01' AS datetime) 
	
	SET @intISOWeekdayNumberOfFirstOfThisThursdaysYear = (((DATEPART(dw, @dtFirstOfThisThursdaysYear) - 1) + (@@DATEFIRST - 1)) % 7) + 1 
	
	-- Get the date of the first Thursday in 'this Thursdays' year. 
	-- The year of which the ISO week is a part is the year of this date.      
	IF (@intISOWeekdayNumberOfFirstOfThisThursdaysYear in (1,2,3,4)) 
		SET @dtFirstThursdayOfYear = DATEADD(d,(4 - @intISOWeekdayNumberOfFirstOfThisThursdaysYear),@dtFirstOfThisThursdaysYear) 
	ELSE 
		SET @dtFirstThursdayOfYear = DATEADD(d,(4 - @intISOWeekdayNumberOfFirstOfThisThursdaysYear + 7),@dtFirstOfThisThursdaysYear) 
		
	-- Work out how many weeks from the first Thursday to this Thursday. 
	SET @intISOWeekNumber = DATEDIFF(d,@dtFirstThursdayOfYear,@dtThisThursday)/7 + 1 
	
	-- Hier kommt die Ermittlung des Jahres 
	IF (DATEPART(mm, @dtDate) = 1 AND @intISOWeekNumber > 50) 
		SET @intYear = DATEPART(yyyy,@dtDate)-1 
	ELSE IF (DATEPART(mm, @dtDate) = 12 AND @intISOWeekNumber = 1)       
		SET @intYear = DATEPART(yyyy, @dtDate)+1 
	ELSE    
		SET @intYear = DATEPART(yyyy, @dtDate) 
		
	RETURN (CAST(@intISOWeekNumber AS nvarchar) + '/' + CAST(@intYear AS nvarchar)) 
END



GO