Better late than never, I guess...
DECLARE @Year CHAR(4)
DECLARE @NextYear CHAR(4)
DECLARE @ISOWeek VARCHAR(2)
SET @Year = '2001'
SET @ISOWeek = '53'
SET @NextYear = @Year+1
SELECT
CASE
WHEN @ISOWeek > 0
AND DATEADD(wk,DATEDIFF(wk,0,'01/04/'+@Year),0)+((@ISOWeek-1)*7)
< DATEADD(wk,DATEDIFF(wk,0,'01/04/'+@NextYear),0)
THEN DATEADD(wk,DATEDIFF(wk,0,'01/04/'+@Year),0)+((@ISOWeek-1)*7)
ELSE 0
END AS StartDate,
DATEADD(wk,DATEDIFF(wk,0,'01/04/'+@Year),0)+((@ISOWeek)*7)-1 AS EndDate
--todo... needs some error checking for the week number...
It returns a "0" or 01/01/1900 for the start date if the week was not found for the year...
I'm thinking that there's no ISO week 53 in 2001 but I might be using the wrong ISO "standard".
--Jeff Moden
Change is inevitable... Change for the better is not.