First and Last Day of the ISO-year

The customer had the request for a report regarding the sales figures Year to Date, per week. More specifically: ISO-week.
More information about ISO-week, go to Wikipedia: ISO-week
Initially I didn't think too much about it, I know Oracle provides the format mask "IW" to get the ISO-week number, and thought that would do. But there is a little bit more to consider.
More information on format masks in Oracle Database 19c: Format Masks

Formatting the date to reflect the ISO-week is easy enough:

to_char (sysdate, 'iw')

which would give you output like '03' or '14'.
Just formatting the dates with this format mask could be sufficient... but there might be a snag to it.

The customer in my case wanted to see the data per year, so the first selection would be the year. Then a report would be shown with the number of order per ISO-week.
To get all the relevant data from the database I would add a filter like this one:

where order_date between to_date ('01-01-'||:P_YEAR, 'dd-mm-yyyy')
                     and to_date ('31-12-'||:P_YEAR, 'dd-mm-yyyy')

to select all the data for a given calendar year. The benefit of using calendar years is that you know they will always start on January first and end on December 31. Simply concatenate the year that the customer selected and turn that string into a proper DATE.

While I was testing the reports for the customer, I noticed that when I selected 2019 as the year, I would also see data for week 01. What's so strange about that? Well, the system didn't go live until August of 2019. There shouldn't be a data for the first week of 2019. Searching though the database backed up my assumption: the earliest orders in the system started in August 2019.

And this is the trap I fell into.. the combination of a Calendar year with ISO-weeks.
Let's take a closer look at the end of 2019 and the start of 2020.

SQL> with test as 
  2  (select date '2019-12-29' as dt from dual
  3  union all select date '2019-12-30' as dt from dual
  4  union all select date '2019-12-31' as dt from dual
  5  union all select date '2020-01-01' as dt from dual
  6  union all select date '2020-01-02' as dt from dual
  7  union all select date '2020-01-03' as dt from dual
  8  union all select date '2020-01-04' as dt from dual
  9  union all select date '2020-01-05' as dt from dual
 10  )
 11  select dt
 12        ,to_char (dt, 'iw') as iso_week
 13        ,to_char (dt, 'ww') as calendar_week
 14   from test
 15 /

DT       ISO_WEEK   CALENDAR_WEEK  
-------- ---------- ---------------
29-12-19 52         52             
30-12-19 01         52             
31-12-19 01         53             
01-01-20 01         01             
02-01-20 01         01             
03-01-20 01         01             
04-01-20 01         01             
05-01-20 01         01             

8 rows selected.

Note the difference in the ISO_WEEK and the CALENDAR_WEEK columns. The first ISO_WEEK starts on December 30 2019, while the first CALENDAR_WEEK starts on January 01 2020.
Also note that Calendar week 53 in 2019 is 1 day long.

Because I was selecting all order data in the selected year, up to December 31 2019, and then format it using ISO-weeks, it would show 01 in the report... It was just not week 01 in 2019, but in 2020.

Getting the First Day of an ISO-year

I decided that to get all the data correctly aligned, I would use the first and last day of the ISO-year. The disadvantage is that the first day of an ISO-year is not fixed, it might start in the year before. The same is true for the last day of the ISO-year, it might end in the year after the one you selected.
Using the format masks (in the documentation link at the top of this blog) it is also possible to pass in an ISO-year.

SQL> select trunc (date '2020-01-01', 'iyyy')
  2    from dual
  3  /

TRUNC(DA
--------
30-12-19

This seems to work, but it doesn't ... or at least not for the purposes that I need it.
If I pass in January 01 2020 I get the first day of the ISO-year, which is December 30 2019. That's correct.
When passing in January 01 2021, I get the following result:

SQL> select trunc (date '2021-01-01', 'iyyy')
  2    from dual
  3  /

TRUNC(DA
--------
30-12-19

And this is not what I expected... Turns out that January 01 2021 is still in ISO-year 2020, and the first day of the that ISO-year is December 30 2019.

The "trick" is not to pass in a date that might not be in the same year as the ISO-year. Don't choose any date in the last calendar week of December or the first calendar week of January.
Picking a random date in the middle of the year will give you the first day ISO-year:

SQL> select trunc (date '2021-08-05', 'iyyy')
  2    from dual
  3  /

TRUNC(DA
--------
04-01-21

Getting the Last Day of an ISO-year

Do the same trick as with getting the first day of the ISO-year.
Get the first day of the following ISO-year that you're interested in and subtract on second:

SQL> select trunc (date '2022-08-05', 'iyyy') - interval '1' second
  2    from dual
  3  /

TRUNC(DA
--------
02-01-22

And the WHERE-clause with the bind variable from the page:

between trunc (to_date (:P_YEAR||'-08-05', 'yyyy-mm-dd'), 'iyyy')
    and trunc (to_date (to_char (to_number (:P_YEAR) + 1)||'-08-05', 'yyyy-mm-dd'), 'iyyy') - interval '1' second