ISO-8601 states that week numbers are to be formated as YYYY-W## - observe that the week number should be two digits as 01, 02, ...
SELECT cast(DATEPART(YYYY, CreationDate) as varchar) + '-W' +
cast(DATEPART(ISO_WEEK, GETDATE())`
The problem is that this gives the week number as 1, 2, ...
What is the correct way of extracting 2020-W01, ...
The original question text relates to a simple formatting issue of the ISO_WEEK number, but I feel there is a bigger issue here with the concatenation of the year part. Simply concatenating DatePart YYYY and ISO_WEEK will yield incorrect (or at least unexpected) results for days in week 1 and/or 53. Notably dates like 2014-12-31 are part of week 1, but not 2014-W01. It is part of 2015-W01. Similarly, 2016-01-01 will be part of 2015-W53, not 2016-W53. In order to determine the Iso Year-Week the year must be corrected to take this into account:
With
Dates (Date) As (
Select Convert( date, N'2014-12-31' )
Union All Select Convert( date, N'2015-01-01' )
Union All Select Convert( date, N'2015-12-31' )
Union All Select Convert( date, N'2016-01-01' )
)
Select
src.Date
, Concat( Case
When DatePart( Month, src.Date ) = 12 And DatePart( ISO_WEEK, src.Date ) = 1 Then DatePart( Year, src.Date ) + 1
When DatePart( Month, src.Date ) = 1 And DatePart( ISO_WEEK, src.Date ) > 50 Then DatePart( Year, src.Date ) - 1
Else DatePart( Year, src.Date )
End, N'-W', Right( Concat( N'00', DatePart( ISO_WEEK, src.Date ) ), 2 ) ) As IsoYearWeek
From
Dates src
;
SELECT cast(DATEPART(YYYY, CreationDate) as varchar) + '-W' + Right('0'+cast(DATEPART(ISO_WEEK,CreationDate) as Varchar),2)
ISO WEEK YEAR is not always the CURRENT YEAR (ie: 01/01/2017 is in the iso week 2016W52), see en.wikipedia.org/wiki/ISO_week_date