First you should note that whenever the day of the week is checked for a specific date, we always make sure that the ISO standard of using Monday as the starting day of the week is used, regardless of the current setting of @@datefirst. This is accomplished by adding @@datefirst - 1 to the value returned by DATEPART(dw, ) and then dividing this number by 7. The remainder of the division is the day of the week for the specified date using Monday as the starting day of the week. Note though that Sunday becomes 0 with this expression, so if you need it to be 7 (for some calculation or whatever) you must exchange it for that.
Maybe, other people like this functionGOCREATE FUNCTION [dbo].[GetDateTimeOfIsoWeek](@WeekYearNumber int, @DayNumber int)RETURNS datetimeAS-------------------------------------------------------------------------------- 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=Monday then 01-04=Thursday -> 01-01=week 1 AND 01-04=week 1 -- if 01-01=Thuesday then 01-04=Friday -> 01-01=week 1 AND 01-04=week 1 -- if 01-01=Wednesday then 01-04=Saterday -> 01-01=week 1 AND 01-04=week 1 -- if 01-01=Thursday then 01-04=Sunday -> 01-01=week 1 AND 01-04=week 1 -- if 01-01=Friday then 01-04=Monday -> 01-01=week 52 AND 01-04=week 1 -- if 01-01=Saterday then 01-04=Thuesday -> 01-01=week 52 AND 01-04=week 1 -- if 01-01=Sunday then 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