I have some problems when I try to implement this solution. I get results in the fourties and up. When I eg. input 2005-01-27 a normal datepart with wk gives the result 5, but this function gives the result 44.

Declare @startdate as smalldatetime
set @startdate ='2005-27-01'

SELECT @startdate,DATEPART(wk, @startdate) as Sql_weeknr,
CASE
-- Exception where @startdate is part of week 52 (or 53) of the previous year
WHEN @startdate <
CASE (DATEPART(dw, CAST(YEAR(@startdate) AS CHAR(4)) + '-01-04') + @@DATEFIRST - 1) % 7
WHEN 1 THEN CAST(YEAR(@startdate) AS CHAR(4)) + '-01-04'
WHEN 2 THEN DATEADD(d, -1, CAST(YEAR(@startdate) AS CHAR(4)) + '-01-04')
WHEN 3 THEN DATEADD(d, -2, CAST(YEAR(@startdate) AS CHAR(4)) + '-01-04')
WHEN 4 THEN DATEADD(d, -3, CAST(YEAR(@startdate) AS CHAR(4)) + '-01-04')
WHEN 5 THEN DATEADD(d, -4, CAST(YEAR(@startdate) AS CHAR(4)) + '-01-04')
WHEN 6 THEN DATEADD(d, -5, CAST(YEAR(@startdate) AS CHAR(4)) + '-01-04')
ELSE DATEADD(d, -6, CAST(YEAR(@startdate) AS CHAR(4)) + '-01-04')
END
THEN
(DATEDIFF(d,
CASE (DATEPART(dw, CAST(YEAR(@startdate) - 1 AS CHAR(4)) + '-01-04') + @@DATEFIRST - 1) % 7
WHEN 1 THEN CAST(YEAR(@startdate) - 1 AS CHAR(4)) + '-01-04'
WHEN 2 THEN DATEADD(d, -1, CAST(YEAR(@startdate) - 1 AS CHAR(4)) + '-01-04')
WHEN 3 THEN DATEADD(d, -2, CAST(YEAR(@startdate) - 1 AS CHAR(4)) + '-01-04')
WHEN 4 THEN DATEADD(d, -3, CAST(YEAR(@startdate) - 1 AS CHAR(4)) + '-01-04')
WHEN 5 THEN DATEADD(d, -4, CAST(YEAR(@startdate) - 1 AS CHAR(4)) + '-01-04')
WHEN 6 THEN DATEADD(d, -5, CAST(YEAR(@startdate) - 1 AS CHAR(4)) + '-01-04')
ELSE DATEADD(d, -6, CAST(YEAR(@startdate) - 1 AS CHAR(4)) + '-01-04')
END,
@startdate
) / 7) + 1
-- Exception where @startdate is part of week 1 of the following year
WHEN @startdate >= CASE (DATEPART(dw, CAST(YEAR(@startdate) + 1 AS CHAR(4)) + '-01-04') + @@DATEFIRST - 1) % 7
WHEN 1 THEN CAST(YEAR(@startdate) + 1 AS CHAR(4)) + '-01-04'
WHEN 2 THEN DATEADD(d, -1, CAST(YEAR(@startdate) + 1 AS CHAR(4)) + '-01-04')
WHEN 3 THEN DATEADD(d, -2, CAST(YEAR(@startdate) + 1 AS CHAR(4)) + '-01-04')
WHEN 4 THEN DATEADD(d, -3, CAST(YEAR(@startdate) + 1 AS CHAR(4)) + '-01-04')
WHEN 5 THEN DATEADD(d, -4, CAST(YEAR(@startdate) + 1 AS CHAR(4)) + '-01-04')
WHEN 6 THEN DATEADD(d, -5, CAST(YEAR(@startdate) + 1 AS CHAR(4)) + '-01-04')
ELSE DATEADD(d, -6, CAST(YEAR(@startdate) + 1 AS CHAR(4)) + '-01-04')
END
THEN 1
ELSE
-- Calculate the ISO week number for all dates that are not part of the exceptions above
(DATEDIFF(d,
CASE (DATEPART(dw, CAST(YEAR(@startdate) AS CHAR(4)) + '-01-04') + @@DATEFIRST - 1) % 7
WHEN 1 THEN CAST(YEAR(@startdate) AS CHAR(4)) + '-01-04'
WHEN 2 THEN DATEADD(d, -1, CAST(YEAR(@startdate) AS CHAR(4)) + '-01-04')
WHEN 3 THEN DATEADD(d, -2, CAST(YEAR(@startdate) AS CHAR(4)) + '-01-04')
WHEN 4 THEN DATEADD(d, -3, CAST(YEAR(@startdate) AS CHAR(4)) + '-01-04')
WHEN 5 THEN DATEADD(d, -4, CAST(YEAR(@startdate) AS CHAR(4)) + '-01-04')
WHEN 6 THEN DATEADD(d, -5, CAST(YEAR(@startdate) AS CHAR(4)) + '-01-04')
ELSE DATEADD(d, -6, CAST(YEAR(@startdate) AS CHAR(4)) + '-01-04')
END,
@startdate
) / 7) + 1
END AS IsoWeek

A bit puzled about this 

Best regards

John Valore

I tried John's code - I had to change the @StartDate to '2005-01-27'.

I had no problems - I received an ISO Week of "4"

Regards,

Nic Washington

Thanks for the reply Nic,

I took some time before someone responded, so I had actually abandoned the approach and settled with the standard datepart function.

Thanks to your response, I looked at the problem again and have now solved the problem. I still have to input the date in the format yyyy-mm-dd :

set @startdate =CAST('2005-01-27' as smalldatetime)

but if I include a statement :
set dateformat mdy

(default american date input/output), the code from Chris Hedgate now works.

The devil is apparently still in the details and the several datetime formats.

Thank you very much.

John Valore

Hi,

Just an update.

I was going to use the functionality in an udf environment, but "forgot" that it is not possible to issue SET commands in an udf.

So I had to look at Chris code again when it suddenly strooke me, that I should replace all the '-01-04' with '-04-01'.

Bingo.

John Valore

Sombody now how to calculate first monday day from given week

For example I need get the day from a given week:

ISO date ‘20050101’ is the week 53 in ISO 8601

If a have the week number 53 how I can calculate the ISO date?

Thank for all in advance.

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".