I would like to get a year number - week number (f.i. 2021-01) using the ISO weeks count. I did manage to get both of them seperate using the following code:

IsoWeek:

(datepart(ISOWK, [Date])) 

IsoYearNumber:

YEAR(DATEADD(day, 26 - DATEPART(isoww, [Date]), [Date]))

However, when I try to combine them into the YYYY-WW format using the line below I will get strange values like 2073 for 2020 WK 53.

(YEAR(DATEADD(day, 26 - DATEPART(isoww, [Date]), [Date])))   '-'   (datepart(ISOWK, [Date])) 

What should I change in the line above to get the ISO Year and Week in the YYYY-WW format? Thanks for the help in advance!

CodePudding user response：

Use the CONCAT function. is both the addition operator and concatenation operator in T-SQL; which is it inferred to be depends on the data that surrounds it. Here it is being inferred as a plus operator, not a concatenation operator, as the data in the query are int values, and the '-' is therefore being implicitly converted to an int too ('-' as an int is 0).

If we use the current date we have the expression below:

(YEAR(DATEADD(day, 26 - DATEPART(isoww, '20211028'), '20211028')))   '-'   (datepart(ISOWK, '20211028'))

Which returns 2064. That's because it resolves to 2021 '-' 43 = 2021 0 43 = 2064.

If you use CONCAT you get what you want, which implicitly converts each value to a string based data type first:

SELECT CONCAT((YEAR(DATEADD(day, 26 - DATEPART(ISOWEEK, '20211028'), '20211028'))), '-', (DATEPART(ISOWEEK, '20211028')));

I also make your casing consistent and use the same keyword for the date part, ISOWEEK, as you you inconsistently use isoww and ISOWK.