GetDateTimeOfIsoWeek

GO
CREATE FUNCTION [dbo].[GetDateTimeOfIsoWeek](@WeekYearNumber int, @DayNumber int)
RETURNS datetime
AS
------------------------------------------------------------------------------
-- GetDateTimeOfIsoWeek
--
-- According to ISO 8601, the first week of the year (week 1) is the week 
-- that contains at least the first four days of the year. In other words, 
-- the week that contains the first Thursday of a year is also week 1 of that year.
-- Also note that according to ISO 8601 a week always starts on a Monday.
-- if 01-01=Mondaythen 01-04=Thursday-> 01-01=week  1 AND 01-04=week 1 
-- if 01-01=Thuesdaythen 01-04=Friday-> 01-01=week  1 AND 01-04=week 1 
-- if 01-01=Wednesdaythen 01-04=Saterday-> 01-01=week  1 AND 01-04=week 1 
-- if 01-01=Thursdaythen 01-04=Sunday-> 01-01=week  1 AND 01-04=week 1 
-- if 01-01=Fridaythen 01-04=Monday-> 01-01=week 52 AND 01-04=week 1 
-- if 01-01=Saterdaythen 01-04=Thuesday-> 01-01=week 52 AND 01-04=week 1 
-- if 01-01=Sundaythen 01-04=Wednesday-> 01-01=week 52 AND 01-04=week 1 
--
-- 4th jan. of indicated year is always in week 1
-- week 1 always starts on Monday, just like other weeks
-- 4th jan. must be Thursday, Friday, Saterday or Sunday if 01-01 is in week 1
-- 1th jan. must be Monday, Thuesday, Wednesday or Thursday if 01-01 is in week 1
--
------------------------------------------------------------------------------
--Created On:19 december 2006
--Created By:martijn Schuurmans
--  Variables:
--  * @WeekYearNumber:
--like 200752, 200453, etc.
--  * @DayNumber:
--1 = Monday
--2 = Thuesday
--3 = Wednesday
--4 = Thursday
--5 = Friday
--6 = Saterday
--7 = Sunday
------------------------------------------------------------------------------
BEGIN
-- Declare the return variable here
DECLARE @DayId int
DECLARE @WeekNumber int
DECLARE @YearNumber int
DECLARE @FunctionResult datetime

-- validate input, we don't like negative WeekYear numbers
SET @WeekYearNumber = ABS(@WeekYearNumber)
IF @DayNumber < 1 
SET @DayNumber = 1

IF @DayNumber > 7
SET @DayNumber = 7

-- get WeekId without Year Number
SET @WeekNumber = right(@WeekYearNumber, 2)

-- get YearId from @YearNumber
SET @YearNumber =
CASE 
-- like: 52
WHEN @WeekYearNumber < 100 THEN YEAR(GETDATE())
-- like: 0752
WHEN @WeekYearNumber < 10000 THEN (LEFT(YEAR(GETDATE()),2)*100) + left(@WeekYearNumber, 2)
-- like: 200752
ELSE LEFT(@WeekYearNumber, 4)
END

-- get datevalue of monday in week 1 of indicated year
-- get datevalue of 1th jan. of indicated year
SET @FunctionResult = CAST((CAST(@YearNumber AS char(4))+ '0101') AS datetime)
-- determine day of week for 01-01-YearNumber
-- make corrections for server settings (=@@DATEFIRST)
SET @DayId = DATEPART(dw, @FunctionResult) + @@DATEFIRST - 1
IF @DayId > 7  
SET @DayId = @DayId - 7

-- determine the correction that is needed on 01-01-YearNumber day to get to Monday of week 1
-- @DayId: 1=Monday; 2=Thuesday; 3=Wednesday; 4=Thursday; 5=Friday; 6=Saterday; 7=Sunday
SET @DayId = 
CASE @DayId
WHEN 1 THEN 0-- if weekday of 01-01=Monday    then: no correction
WHEN 2 THEN -1-- if weekday of 01-01=Thuesday  then: Monday of W1 is 1 day before 01-01
WHEN 3 THEN -2-- if weekday of 01-01=Wednesday then: Monday of W1 is 2 days before 01-01
WHEN 4 THEN -3-- if weekday of 01-01=Thursday  then: Monday of W1 is 3 days before 01-01
WHEN 5 THEN 3-- if weekday of 01-01=Friday    then: Monday of W1 is 3 days after 01-01
WHEN 6 THEN 2-- if weekday of 01-01=Saterday  then: Monday of W1 is 2 days after 01-01
WHEN 7 THEN 1-- if weekday of 01-01=Sunday    then: Monday of W1 is 1 day after 01-01
END

-- this is datevalue of monday in week 1 of indicated year
SET @FunctionResult = DATEADD(dd, @DayId, @FunctionResult)

-- get last day of week, this is always sunday
-- and make correction for given @DayNumer
SET @FunctionResult = DATEADD(dd, ((@WeekNumber * 7) + @DayNumber - 8), @FunctionResult)

RETURN(@FunctionResult)
END