First day of year is Monday +-----+-----+ | Yes | No | |-----+-----+Yes | F | F | +-----+-----+No | W | F | +-----+-----+Leap year

--===== Build all dates from 1753-01-01 through 9999-12-31 WITH cteGenDates AS( SELECT TOP (DATEDIFF(dd,'1753','9999-12-31')+1) Date = DATEADD(dd,ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1,'1753') FROM master.sys.all_columns ac1, master.sys.all_columns ac2) --=== Do the calculated ISOWk from the article and the built in function -- and capture enough other data to find where the calculation is incorrect. SELECT Date, DOW = DATENAME(dw,Date), CalculatedISOWk = (DATEPART(dy,DATEDIFF(dd,0,Date)/7*7+3)+6)/7, BuiltInISOWk = DATEPART(isowk,Date), IsLeapYear = ISDATE(DATENAME(yy,Date)+'0229') INTO #MyHead FROM cteGenDates;--===== Find first or last weeks of each year that have an error. SELECT * FROM #MyHead WHERE (DATEPART(dy,Date) <= 8 OR DATEPART(dy,Date) >= 356) AND CalculatedISOWk <> BuiltInISOWk ORDER BY Date;

(DATEPART(dy,DATEADD(dd,DATEDIFF(dd,'1753',Date)/7*7+3 ,'1753'))+6)/7

CREATE FUNCTION dbo.fnISOWEEK( @Date DATE)RETURNS TINYINTASBEGIN RETURN ( SELECT CASE WHEN nextYear <= theDate THEN 0 WHEN currYear <= theDate THEN (theDate - currYear) / 7 ELSE (theDate - prevYear) / 7 END + 1 FROM ( SELECT (CASE WHEN prevYear % 400 = 0 THEN -366 WHEN prevYear % 100 = 0 THEN -365 WHEN prevYear % 4 = 0 THEN -366 ELSE -365 END + theDate - theDoY + 4) / 7 * 7 AS prevYear, (theDate - theDoY + 4) / 7 * 7 AS currYear, (CASE WHEN currYear % 400 = 0 THEN 366 WHEN currYear % 100 = 0 THEN 365 WHEN currYear % 4 = 0 THEN 366 ELSE 365 END + theDate - theDoY + 4) / 7 * 7 AS nextYear, theDate FROM ( SELECT DATEPART(YEAR, @Date) - 1 AS prevYear, DATEPART(YEAR, @Date) AS currYear, DATEDIFF(DAY, '00010101', @Date) AS theDate, DATEPART(DAYOFYEAR, @Date) AS theDoY ) AS d ) AS d )END

CREATE FUNCTION dbo.fnISOWEEK ( @Year SMALLINT, @Month TINYINT, @Day TINYINT)RETURNS TINYINTASBEGIN RETURN ( SELECT CASE WHEN nextYearStart <= theDate THEN 0 WHEN currYearStart <= theDate THEN (theDate - currYearStart) / 7 ELSE (theDate - prevYearStart) / 7 END + 1 FROM ( SELECT (currJan4 - 365 - prevLeapYear) / 7 * 7 AS prevYearStart, currJan4 / 7 * 7 AS currYearStart, (currJan4 + 365 + currLeapYear) / 7 * 7 AS nextYearStart, CASE @Month WHEN 1 THEN @Day WHEN 2 THEN 31 + @Day WHEN 3 THEN 59 + @Day + currLeapYear WHEN 4 THEN 90 + @Day + currLeapYear WHEN 5 THEN 120 + @Day + currLeapYear WHEN 6 THEN 151 + @Day + currLeapYear WHEN 7 THEN 181 + @Day + currLeapYear WHEN 8 THEN 212 + @Day + currLeapYear WHEN 9 THEN 243 + @Day + currLeapYear WHEN 10 THEN 273 + @Day + currLeapYear WHEN 11 THEN 304 + @Day + currLeapYear WHEN 12 THEN 334 + @Day + currLeapYear END + currJan4 - 4 AS theDate FROM ( SELECT CASE WHEN (@Year - 1) % 400 = 0 THEN 1 WHEN (@Year - 1) % 100 = 0 THEN 0 WHEN (@Year - 1) % 4 = 0 THEN 1 ELSE 0 END AS prevLeapYear, CASE WHEN @Year % 400 = 0 THEN 1 WHEN @Year % 100 = 0 THEN 0 WHEN @Year % 4 = 0 THEN 1 ELSE 0 END AS currLeapYear, 365 * (@Year - 1) + (@Year - 1) / 400 - (@Year - 1) / 100 + (@Year - 1) / 4 + 3 AS currJan4 WHERE @Year BETWEEN 0 AND 9999 AND @Month BETWEEN 1 AND 12 AND @Day >= 1 AND 1 = CASE WHEN @Month IN (1, 3, 5, 7, 8, 10, 12) AND @Day <= 31 THEN 1 WHEN @Month IN (4, 6, 9, 11) AND @Day <= 30 THEN 1 WHEN @Year % 400 = 0 AND @Day <= 29 THEN 1 WHEN @Year % 100 = 0 AND @Day <= 28 THEN 1 WHEN @Year % 4 = 0 AND @Day <= 29 THEN 1 WHEN @Day <= 28 THEN 1 ELSE 0 END ) AS d ) AS d )END