Ibm Db2: Generate List of Dates Between Two Dates

IBM DB2: Generate list of dates between two dates

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.

DB2: How do I display all dates in a range

I did a test -- this works on 9.7

with table1(start_dt,end_dt, amount) as
(
  values (timestamp('2017-01-01'), timestamp('2017-01-03'), 600)

), this_is_not_a_reserved_word (start_dt, end_dt, d, amount) as
(
  SELECT start_dt, end_dt, start_dt as d,
         amount/ (timestampdiff(16,end_dt-start_dt)+1) as amount
  FROM table1
--  WHERE tab_id_id = 518621     

    UNION ALL

  SELECT start_dt, end_dt, d + 1 day , amount
  FROM this_is_not_a_reserved_word
  WHERE d < end_dt
)
SELECT d, amount
FROM this_is_not_a_reserved_word

---

original answer

Here you go:

with this_is_not_a_reserved_word as
(
  SELECT start_dt, end_dt, start_dt as dt, amount/timestampdiff(16,start_dt-end_dt) as amount
  FROM table1
  WHERE tab_id_id = 518621     

    UNION 

  SELECT start_dt, end_dt, dt + 1 day as dt, amount
  FROM this_is_not_a_reserved_word
  WHERE dt < end_dt
)
SELECT dt, amount
FROM this_is_not_a_reserved_word

If start_dt and end_dt are type date and not timestamp use:

amount/timestampdiff(16,timestamp(start_dt)-timestamp(end_dt)) as amount

Get a list of dates between two dates using a function

Try something like this:

CREATE FUNCTION dbo.ExplodeDates(@startdate datetime, @enddate datetime)
returns table as
return (
with 
 N0 as (SELECT 1 as n UNION ALL SELECT 1)
,N1 as (SELECT 1 as n FROM N0 t1, N0 t2)
,N2 as (SELECT 1 as n FROM N1 t1, N1 t2)
,N3 as (SELECT 1 as n FROM N2 t1, N2 t2)
,N4 as (SELECT 1 as n FROM N3 t1, N3 t2)
,N5 as (SELECT 1 as n FROM N4 t1, N4 t2)
,N6 as (SELECT 1 as n FROM N5 t1, N5 t2)
,nums as (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) as num FROM N6)
SELECT DATEADD(day,num-1,@startdate) as thedate
FROM nums
WHERE num <= DATEDIFF(day,@startdate,@enddate) + 1
);

You then use:

SELECT *
FROM dbo.ExplodeDates('20090401','20090531') as d;

Edited (after the acceptance):

Please note... if you already have a sufficiently large nums table then you should use:

CREATE FUNCTION dbo.ExplodeDates(@startdate datetime, @enddate datetime)
returns table as
return (
SELECT DATEADD(day,num-1,@startdate) as thedate
FROM nums
WHERE num <= DATEDIFF(day,@startdate,@enddate) + 1
);

And you can create such a table using:

CREATE TABLE dbo.nums (num int PRIMARY KEY);
INSERT dbo.nums values (1);
GO
INSERT dbo.nums SELECT num + (SELECT COUNT(*) FROM nums) FROM nums
GO 20

These lines will create a table of numbers containing 1M rows... and far quicker than inserting them one by one.

You should NOT create your ExplodeDates function using a function that involves BEGIN and END, as the Query Optimizer becomes unable to simplify the query at all.

Calculating how many days are between two dates in DB2?

I think that @Siva is on the right track (using DAYS()), but the nested CONCAT()s are making me dizzy. Here's my take.

Oh, there's no point in referencing sysdummy1, as you need to pull from a table regardless.

Also, don't use the implicit join syntax - it's considered an SQL Anti-pattern.

I'be wrapped the date conversion in a CTE for readability here, but there's nothing preventing you from doing it inline.

WITH Converted (convertedDate) as (SELECT DATE(SUBSTR(chdlm, 1, 4) || '-' ||
                                               SUBSTR(chdlm, 5, 2) || '-' ||    
                                               SUBSTR(chdlm, 7, 2))
                                   FROM Chcart00
                                   WHERE chstat = '05')

SELECT DAYS(CURRENT_DATE) - DAYS(convertedDate)
FROM Converted

Get previous two Sundays Date DB2

Try this:

SELECT
  RMD.ISSUE_ID
, MAX(CASE WHEN AS_OF_DATE = t.last_sunday THEN CURRENT.ISSUE_TITLE END) AS CURR_ISSUE_TITLE
...

FROM LOD.ISM_ISSUE_SUMMARY_HIST_WKY CURRENT
JOIN LOD.RMD_ISS_REMED_SUMMARY RMD
 ON CURRENT.ISSUE_ID = RMD.ISSUE_ID

JOIN
(
  values
  (
    current date - (dayofweek_iso (current date)    ) days
  , current date - (dayofweek_iso (current date) + 7) days
  )
) t (last_sunday, prev_sunday)
ON AS_OF_DATE IN (t.last_sunday, t.prev_sunday)

GROUP BY RMD.ISSUE_ID
HAVING MAX(CASE WHEN AS_OF_DATE = t.prev_sunday THEN CURRENT.MEMBER_IMPACT END) <> MAX(CASE WHEN AS_OF_DATE = t.last_sunday THEN CURRENT.MEMBER_IMPACT END) ;

Refer to Date operations and durations and DAYOFWEEK_ISO scalar function.

---

Related Topics