drop function dbo.F_ISO_WEEK_OF_YEAR
go
create function dbo.F_ISO_WEEK_OF_YEAR
	(
	@Date	datetime
	)
returns		int
as
/*
Function F_ISO_WEEK_OF_YEAR returns the
ISO 8601 week of the year for the date passed.
*/
begin

declare @WeekOfYear		int

select
	-- Compute week of year as (days since start of year/7)+1
	-- Division by 7 gives whole weeks since start of year.
	-- Adding 1 starts week number at 1, instead of zero.
	@WeekOfYear =
	(datediff(dd,
	-- Case finds start of year
	case
	when	NextYrStart <= @date
	then	NextYrStart
	when	CurrYrStart <= @date
	then	CurrYrStart
	else	PriorYrStart
	end,@date)/7)+1
from
	(
	select
		-- First day of first week of prior year
		PriorYrStart =
		dateadd(dd,(datediff(dd,-53690,dateadd(yy,-1,aa.Jan4))/7)*7,-53690),
		-- First day of first week of current year
		CurrYrStart =
		dateadd(dd,(datediff(dd,-53690,aa.Jan4)/7)*7,-53690),
		-- First day of first week of next year
		NextYrStart =
		dateadd(dd,(datediff(dd,-53690,dateadd(yy,1,aa.Jan4))/7)*7,-53690)
	from
		(
		select
			--Find Jan 4 for the year of the input date
			Jan4	= 
			dateadd(dd,3,dateadd(yy,datediff(yy,0,@date),0))
		) aa
	) a

return @WeekOfYear

end
go





-- Execute function on first day of first week of year from 1990 to 2030
select
	DT,
	ISO_WEEK_OF_YEAR = 
		dbo.F_ISO_WEEK_OF_YEAR(a.DT)
from
	(
	select DT = getdate()	union all
	select DT = convert(datetime,'1990/01/01')  union all
	select DT = convert(datetime,'1990/12/31')  union all
	select DT = convert(datetime,'1991/12/30')  union all
	select DT = convert(datetime,'1993/01/04')  union all
	select DT = convert(datetime,'1994/01/03')  union all
	select DT = convert(datetime,'1995/01/02')  union all
	select DT = convert(datetime,'1996/01/01')  union all
	select DT = convert(datetime,'1996/12/30')  union all
	select DT = convert(datetime,'1997/12/29')  union all
	select DT = convert(datetime,'1999/01/04')  union all
	select DT = convert(datetime,'2000/01/03')  union all
	select DT = convert(datetime,'2001/01/01')  union all
	select DT = convert(datetime,'2001/12/31')  union all
	select DT = convert(datetime,'2002/12/30')  union all
	select DT = convert(datetime,'2003/12/29')  union all
	select DT = convert(datetime,'2005/01/03')  union all
	select DT = convert(datetime,'2006/01/02')  union all
	select DT = convert(datetime,'2007/01/01')  union all
	select DT = convert(datetime,'2007/12/31')  union all
	select DT = convert(datetime,'2008/12/29')  union all
	select DT = convert(datetime,'2010/01/04')  union all
	select DT = convert(datetime,'2011/01/03')  union all
	select DT = convert(datetime,'2012/01/02')  union all
	select DT = convert(datetime,'2012/12/31')  union all
	select DT = convert(datetime,'2013/12/30')  union all
	select DT = convert(datetime,'2014/12/29')  union all
	select DT = convert(datetime,'2016/01/04')  union all
	select DT = convert(datetime,'2017/01/02')  union all
	select DT = convert(datetime,'2018/01/01')  union all
	select DT = convert(datetime,'2018/12/31')  union all
	select DT = convert(datetime,'2019/12/30')  union all
	select DT = convert(datetime,'2021/01/04')  union all
	select DT = convert(datetime,'2022/01/03')  union all
	select DT = convert(datetime,'2023/01/02')  union all
	select DT = convert(datetime,'2024/01/01')  union all
	select DT = convert(datetime,'2024/12/30')  union all
	select DT = convert(datetime,'2025/12/29')  union all
	select DT = convert(datetime,'2027/01/04')  union all
	select DT = convert(datetime,'2028/01/03')  union all
	select DT = convert(datetime,'2029/01/01')  union all
	select DT = convert(datetime,'2029/12/31')  union all
	select DT = convert(datetime,'2030/12/30')
	) a

RETURNS TINYINT
AS
BEGIN
	RETURN	(
			SELECT	CASE
					WHEN @theDate >= '99990104' THEN (DATEPART(DAYOFYEAR, @theDate) - 4) / 7 
					WHEN @theDate >= '99990101' THEN 52
					WHEN NextYear <= @theDate THEN 0
					WHEN CurrentYear <= @theDate THEN DATEDIFF(DAY, CurrentYear, @theDate) / 7
					ELSE DATEDIFF(DAY, PreviousYear, @theDate) / 7
				END + 1
			FROM	(
					SELECT	DATEADD(DAY, (DATEDIFF(DAY, '17530101', DATEADD(YEAR, -1, Jan4)) / 7) * 7, '17530101') AS PreviousYear,
						DATEADD(DAY, (DATEDIFF(DAY, '17530101', Jan4) / 7) * 7, '17530101') AS CurrentYear,
						DATEADD(DAY, (DATEDIFF(DAY, '17530101', DATEADD(YEAR, 1, Jan4)) / 7) * 7, '17530101') AS NextYear
					FROM	(
							SELECT	DATEADD(YEAR, DATEDIFF(YEAR, 0, @theDate), 3) AS Jan4
						) AS x
				) AS d
		)
END

quote:

---

Originally posted by huslayer

Public Function ISO_DateOfWeek( _
ByVal intYear As Integer, _
ByVal bytWeek As Byte, _
Optional ByVal bytWeekday As Byte = vbMonday) _
As Date

' Calculates date of requested weekday in a week of
' a year according to ISO 8601:1988 standard.
...

---

;WITH cteCalendar
AS (
	SELECT	DATEADD(DAY, DATEDIFF(DAY, 0, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 7 * Number)) / 7 * 7, 0) AS theMonday,
		1 + Number AS theWeek
	FROM	master..spt_values
	WHERE	Type = 'P'
		AND number BETWEEN 0 AND 52
)
SELECT		theWeek,
		theMonday,
		DATEADD(DAY, 6, theMonday) AS theSunday
FROM		cteCalendar
WHERE		2011 IN (DATEPART(YEAR, theMonday), DATEPART(YEAR, DATEADD(DAY, 6, theMonday)))
ORDER BY	theWeek