SQL statement for generating rows of intermediate dates

[Henry]

Dear all,

Is there any DB2 SQL statement that could generate rows of intermediate
dates by providing the start and end dates??

If 2006-1-1 and 2006-1-31 are provided to the SQL, the output will look
like :

2006-1-1
2006-1-2
2006-1-3
...
...
...
2006-1-31

Thanks in advance!

Henry

[Tonkuma]

------------------------- Commands Entered -------------------------
SELECT start_date + (n2*10+n1) DAYS AS Inter_dates
FROM (VALUES (DATE('2006-1-1'), DATE('2006-1-31'))) Q (Start_date,
End_date)
, (VALUES 0,1,2,3,4,5,6,7,8,9) P1(n1)
, (VALUES 0,1,2,3,4,5,6,7,8,9) P2(n2)
WHERE (n2*10+n1) <= DAYS(End_date) - DAYS(Start_date)
ORDER BY
Inter_dates
;
--------------------------------------------------------------------

INTER_DATES
-----------
2006-01-01
2006-01-02
2006-01-03
2006-01-04
2006-01-05
2006-01-06
2006-01-07
2006-01-08
2006-01-09
2006-01-10
2006-01-11
2006-01-12
2006-01-13
2006-01-14
2006-01-15
2006-01-16
2006-01-17
2006-01-18
2006-01-19
2006-01-20
2006-01-21
2006-01-22
2006-01-23
2006-01-24
2006-01-25
2006-01-26
2006-01-27
2006-01-28
2006-01-29
2006-01-30
2006-01-31

31 record(s) selected.
Or, you can use recursive query.

[Shyam Peri]

Very intelligent thought

[Serge Rielau]

Henry wrote:

Dear all,

Is there any DB2 SQL statement that could generate rows of intermediate
dates by providing the start and end dates??

If 2006-1-1 and 2006-1-31 are provided to the SQL, the output will look
like :

2006-1-1
2006-1-2
2006-1-3
..
..
..
2006-1-31

CREATE FUNCTION dates(start DATE, end DATE)
RETURNS TABLE(dt DATE)
RETURN WITH rec(dt) AS (VALUES (start)
UNION ALL
SELECT dt + 1 DAY FROM rec
WHERE dt < end)
SELECT dt FROM rec;

SELECT dt, item, sales
FROM TABLE(dates(DATE('2003-01-01'),
DATE('2003-12-31'))) AS dates
LEFT OUTER JOIN sales ON dates.dt = sales.dt;

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab

[Henry]

Thanks you very much for yours' brilliant solutions!!

Henry

- 26 Jan 2006

[Chris]

A small change that eliminates the warning message...

But only good for a 10,000 years of dates.... Darn

CREATE FUNCTION DATES(START DATE, END DATE)
RETURNS TABLE(DT DATE)
RETURN
WITH REC(DT, LEVEL) AS
(
VALUES (START, 1)
UNION ALL
SELECT DT + 1 DAY, LEVEL + 1
FROM REC
WHERE DT + 1 DAY <= END
AND LEVEL < 3650000
)
SELECT DT FROM REC;

just my two cents... Enjoy