Playing with dates in SQL DB2 for i

0
98

Some time we need a day per day report … and we need all dates in a period of time.

We can use this simple trick to get all date, suppose next 30 days:

WITH dummy(day) AS (                                     
    SELECT current date AS day    FROM SYSIBM.SYSDUMMY1  
    UNION ALL                                            
    SELECT day    + 1 days FROM dummy WHERE day   <=     
 current date + 30 days                                  
)                                                        
SELECT day    FROM dummy

Here what we get (dd/mm/yy format)

DAY
29/01/17
30/01/17
31/01/17
01/02/17
02/02/17
03/02/17
04/02/17
05/02/17
06/02/17
07/02/17
08/02/17