ISO compliant year-week function in DB2

 I've already shown how to create a year-month function in DB2, which - when it comes to date arithmatic - is quite straightforward, because very year (in ISO/Gregorian) calendar starts with the first month.

Some systems argue whether this should have the ordinal 1 or 0, but thats the usual 0/1 issue in programming.

Weeks however, are far more complex, because not every year starts with the begin of a week (whether this is Sunday or Monday in your preference / area).

It might just start with a Thursday... WOW.

So for that ths ISO 8601 standard set a definition on what is to be considered week 1 of a year:

The ISO 8601 definition for week 01 is the week with the first Thursday of the Gregorian year (i.e. of January) in it

Luckily, DB2 has a function for that - WEEK_ISO.

So let's just try that with a 

rtrim(char(year(TS))) || right(digits(week_iso(TS)),2)

Takes the year (need to rtrim it) and adds 2 digits week to it (you might want to insert a "w") between them.

However, this leeds to e.g. 2021-01-03 being in week 53, because week 1 start on 2021-01-04.

the yearweek for 2021-01-03 therefore should be 2020-53 not 2021-53 as the above formular would yield.

Now we need to make sure that if we get a week 53 and its January we return the previous year... Only for January, because some days in December might also be week 53, and we need to keep the year there.

Voila:

create function yearweek_iso(TS timestamp)
returns varchar(6) no external action deterministic 
return    
CASE

     WHEN (week_iso(TS)=53 AND month(TS)=1) Then
              rtrim(char(year (TS)-1)) || right(digits(week_iso(TS)),2)   
     ELSE
              rtrim(char(year(TS))) || right(digits(week_iso(TS)),2)
END

The results now match whatever java.time package might do with week parsing. In order to get the first day of this week back (in Java, where I needed it), you parse as follow:

new DateTimeFormatterBuilder()
            .appendValue(IsoFields.WEEK_BASED_YEAR, 4)
            .appendValue(IsoFields.WEEK_OF_WEEK_BASED_YEAR,2)
            .parseDefaulting(WeekFields.ISO.dayOfWeek(), 1)
            .toFormatter();