SQL statement for generating rows of intermediate dates

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Henry - 25 Jan 2006 08:16 GMT

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

Reply to this Message

Tonkuma - 25 Jan 2006 09:03 GMT

------------------------- 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.

Reply to this Message

Shyam Peri - 25 Jan 2006 11:50 GMT

Very intelligent thought

Reply to this Message

Serge Rielau - 25 Jan 2006 13:21 GMT

> Dear all,
>
[quoted text clipped - 11 lines]
> ..
> 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

Signature

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

Reply to this Message

Henry - 26 Jan 2006 01:37 GMT

Thanks you very much for yours' brilliant solutions!!

Henry

- 26 Jan 2006

Reply to this Message

Chris - 26 Jan 2006 03:51 GMT

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

Reply to this Message