IBM DB2: Generate list of dates between two dates

Question

I need a query which will output a list of dates between two given dates.

For example, if my start date is 23/02/2016 and end date is 02/03/2016, I am expecting the following output:

Date
----
23/02/2016
24/02/2016
25/02/2016
26/02/2016
27/02/2016
28/02/2016
29/02/2016
01/03/2016
02/03/2016

Also, I need the above using SQL only (without the use of 'WITH' statement or tables). Please help.

Solution 1

I am using ,ostly DB2 for iSeries, so I will give you an SQL only solution that works on it. Currently I don't have an access to the server, so the query is not tested but it should work. EDIT Query is already tested and working

SELECT
    d.min + num.n DAYS
FROM
    -- create inline table with min max date
    (VALUES(DATE('2015-02-28'), DATE('2016-03-01'))) AS d(min, max)
INNER JOIN
    -- create inline table with numbers from 0 to 999
    (
        SELECT
            n1.n + n10.n + n100.n AS n
        FROM
            (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS n1(n)
        CROSS JOIN
            (VALUES(0),(10),(20),(30),(40),(50),(60),(70),(80),(90)) AS n10(n)
        CROSS JOIN
            (VALUES(0),(100),(200),(300),(400),(500),(600),(700),(800),(900)) AS n100(n)
    ) AS num
ON
    d.min + num.n DAYS<= d.max
ORDER BY
    num.n;

if you don't want to execute the query only once, you should consider creating a real table with values for the loop:

CREATE TABLE dummy_loop AS (
    SELECT
        n1.n + n10.n + n100.n AS n
    FROM
        (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS n1(n)
    CROSS JOIN
        (VALUES(0),(10),(20),(30),(40),(50),(60),(70),(80),(90)) AS n10(n)
    CROSS JOIN
        (VALUES(0),(100),(200),(300),(400),(500),(600),(700),(800),(900)) AS n100(n)
) WITH DATA;

ALTER TABLE dummy_loop ADD PRIMARY KEY (dummy_loop.n);

It depends on the reason for which you like to use it, but you could even create table for lets say for 100 years. It will be only 100*365 = 36500 rows with just a date field, so the table will be quite small and fast for joins.

CREATE TABLE dummy_dates AS (
    SELECT
        DATE('1970-01-01') + (n1.n + n10.n + n100.n) DAYS AS date
    FROM
        (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS n1(n)
    CROSS JOIN
        (VALUES(0),(10),(20),(30),(40),(50),(60),(70),(80),(90)) AS n10(n)
    CROSS JOIN
        (VALUES(0),(100),(200),(300),(400),(500),(600),(700),(800),(900)) AS n100(n)
) WITH DATA;

ALTER TABLE dummy_dates ADD PRIMARY KEY (dummy_dates.date);

And the select query could look like:

SELECT
    *
FROM
    dummy_days
WHERE
    date BETWEEN(:startDate, :endDate);

EDIT 2: Thanks to @Lennart suggestion I have changed TABLE(VALUES(..,..,..)) to VALES(..,..,..) because as he said TABLE is a synonym to LATERAL that was a real surprise for me.

EDIT 3: Thanks to @godric7gt I have removed TIMESTAMPDIFF and will remove from all my scripts, because as it is said in the documentation:

These assumptions are used when converting the information in the second argument, which is a timestamp duration, to the interval type specified in the first argument. The returned estimate may vary by a number of days. For example, if the number of days (interval 16) is requested for the difference between '1997-03-01-00.00.00' and '1997-02-01-00.00.00', the result is 30. This is because the difference between the timestamps is 1 month, and the assumption of 30 days in a month applies.

It was a real surprise, because I was always trust this function for days difference.

Solution 2

For generating rows recusive SQL will needed. Usually this looks like this in DB2:

with temp (date) as (
select date('23.02.2016') as date from sysibm.sysdummy1
union all
select date + 1 day from temp
where date < date('02.03.2016') 
)

select * from temp

For whatever reason a CTE (using WITH) should be avoided. A possible workaround would be setting

db2set DB2_COMPATIBILITY_VECTOR=8

which enables the use of the Oracle style recusion with CONNECT BY

SELECT date('22.02.2016') + level days  as dt
  FROM sysibm.sysdummy1 CONNECT BY date('22.02.2016') + level days <= date('02.03.2016')

Please note: after setting the DB2_COMPATIBILITY_VECTOR a instance restart is necessary.

Comments

[Goutam]

Thank you for responding, yes I need an SQL only solution, I tried executing your query but I got the following error: " 16:47:41 [SELECT - 0 row(s), 0.580 secs] 1) [Error Code: -440, SQL State: 42884] DB2 SQL Error: SQLCODE=-440, SQLSTATE=42884, SQLERRMC=TIMESTAMPDIFF;FUNCTION, DRIVER=4.18.60. 2) [Error Code: -727, SQL State: 56098] DB2 SQL Error: SQLCODE=-727, SQLSTATE=56098, SQLERRMC=2;-440;42884;TIMESTAMPDIFF|FUNCTION, DRIVER=4.18.60 ... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.580/0.000 sec [0 successful, 0 warnings, 1 errors] " Can you help debug?

[Goutam]

I tried executing the query as select '23/02/2016' + n days from ( select row_number() over () as n from sysibm.dual ) as t where n < 100; but I am getting an error. Can you help debug this?

[Goutam]

Sorry I need an SQL only solution, unfortunately due to some constraints on the environment I am on, I am unable to test this, thank you for your effort though :)

[Goutam]

Thank you for replying :) I actually tried with connect by and level myself, but it doesnt seem to be working on my environment. My IDE is not giving me any proper error message either, so I am having a really hard time to debug.

[Mita]

Yes, of course, immediately after our network team got our VPN to the server working again :) I just need a working DB2 server to debug...

[Lennart - Slava Ukraini]

What error did you get? BTW, dual contains only one row som that won't do as a generator

[Lennart - Slava Ukraini]

You might have to cast the date to a date type, like select date('2016-02-23') + ...

[Goutam]

did the casting and select date('20160223') + n days from ( select row_number() over () as n from reporter_status ) as t where n < 100; is returning error " 17:46:59 [SELECT - 0 row(s), 0.577 secs] [Error Code: -180, SQL State: 22007] DB2 SQL Error: SQLCODE=-180, SQLSTATE=22007, SQLERRMC=null, DRIVER=4.18.60 ... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.577/0.000 sec [0 successful, 0 warnings, 1 errors] " does this help?

[Lennart - Slava Ukraini]

Try using some standard date representation like: '2016-02-13', the error means: SQL0180N The syntax of the string representation of a datetime value is incorrect.

[Goutam]

Thanks Lennart, its working now :) Made a few changes as per my requirement and the final query is like this select date('2016-01-31') -(n-1) days from ( select row_number() over () as n from reporter_status ) as t where n<=days ('2016-01-31') - days (date('2015-02-01')-1 day)

[Mita]

@godric7gt I have connected to the server, tested and fixed the query. You could try it by yourself, hope it will fix your issues. P.S. If you need to run it regularry consider having real table with dates as I show in last example.

[Lennart - Slava Ukraini]

@Mita, unless it is necessary for db2 for I, you can simplify your queries a bit. Table is a db2 synonym for lateral (works as well) and is not necessary in your query. Furthermore I believe that you can do: select n from (values 1,2,3) t(n) which is a bit more dense.

[Mita]

@Lennart Yes, the short syntax could be used, but I prefer the full. The short not does not give any benefits to the query, but could worse query readability. I prefer every time to use the full syntax with uppercase for the keywords and lowercase for the identifiers. It helps me read very easy even huge queries.

[Lennart - Slava Ukraini]

Using TABLE may cause additional burden on the optimizer since it opens up new possibilities that has to be taken into consideration. For a complex query this may degrade performance. You are probably aware of the purpose of TABLE (LATERAL), but I see it misused a lot due to unawareness of what it really means. That's why I made a comment on it.

[Mita]

@Lannart Thank you, I have checked the documentation and really TABLE is synonym of LATERAL :) Never know that...

[Lennart - Slava Ukraini]

Yes, they can both be be used in this context (I prefer LATERAL since it is standard). But when selecting from a table function you have to use the keyword TABLE. In a sense TABLE as in table function also allow us to reference variables otherwise hidden. I have not seen table functions mentioned in standard documents, so perhaps that's why IBM exclusively use TABLE for functions and allow TABLE as an alias for LATERAL otherwise.

[Goutam]

@Mita, thank you for the updated query, I tried using the date range '2015-02-01' and '2016-01-31', but its giving the output till 2016-01-27, please suggest. I did consider table, but considering in a lot of factors an sql only solution seems to be the only reasonable and troublefree solution.

[Mita]

@godric7gt I have just found that TIMESTAMPDIFF has an issue with date difference calculation! Thank you you have pointed me on that :) I will post a solution later, because I use it on production and rely that it will return me real days difference. I have an idea for a deterministic stored function that will return a real day difference.

[Clockwork-Muse]

DB2 (all recent versions) supports recursive CTEs.

[Mita]

@godric7gt The query is no more using TIMESTAMPDIFF and the returned period do not contain errors

[Goutam]

@Mita, thank you, will check this out and revert back :)

[Goutam]

@Mita, thank you, this is working perfectly :) Sorry for the delay in responding.