Search code examples
sql-serverdatet-sqlcalendar

SQL - How to get the Year-Week using the ISO weeks?

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!

Solution

  • 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(ISO_WEEK, '20211028'), '20211028'))), '-', (DATEPART(ISO_WEEK, '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.