SQL: Returns a list of all dates in a given year

days_in_year.sql

-- Returns a list of all dates in a given year
--
-- Note: I am using library QUSRSYS. I suggest you put it into your own tool library
--
-- Simply paste this gist into ACS SQL and step through the example.
--
-- It is a cool example how far you can go with SQL: Have fun 😀  
-- (C) Niels Liisberg 2022
--
-- This gist is distributed on an "as is" basis, without warranties 
-- or conditions of any kind, either expressed or implied.
----------------------------------------------------------------------------------------------
create or replace function QUSRSYS.days_in_year  (
    year_YYYY char(4)
) 
returns table ( date_in_year date  )
set option output=*print, commit=*none, datfmt=*iso, dbgview = *source --list
begin

    declare first_date date;
    set first_date = year_YYYY concat '-01-01';
    
    while year(first_date) = year_YYYY   do
        pipe (first_date);
        set first_date = first_date + 1 day;
    end while;
    return;
end;

-- Test case
select * from table( 
    QUSRSYS.days_in_year (
        year_YYYY => '2020'
    )
);