Generating a sequential date series in Oracle

2009-02-11 19:00:00 -0500

I needed a bit of SQL to gin up a series of sequential dates in Oracle that I could use in generating data for flash charts in APEX. It’s important to have a solid series of dates that you can LEFT OUTER JOIN against so that NULL values correspond to one of your series points, keeping all the data properly aligned.

In looking around for some good examples I found some way more complex date series tricks
, but didn’t see any article on the simple basics of doing a series of dates between a start and end date. Here’s what I found along the way:

-- notes for generating date sequences in oracle
-- 
-- gleaned from a pretty interesting article by Indrajit Agasti on generating
-- various series of working (non-weekend) days:
-- http://www.dba-oracle.com/t_test_data_date_generation_sql.htm
--
-- i'm sure this old hat for many oracle devs but i didn't see it out there 
-- anywhere so i figured a quick ref of notes i took in the process might 
-- be useful to somebody else

-- to knock a string into a date object:
SELECT TO_DATE('01-JUN-2008') FROM DUAL;

-- getting an integer difference between two dates:
SELECT TO_DATE('01-JUN-2008') - TO_DATE('01-MAY-2008') FROM DUAL;

-- generating a sequential series using dim (this is odd, admittedly)
-- 0, 1, 2 ... 9  (n - 1)
SELECT dim FROM DUAL
MODEL
DIMENSION BY (0 dim) MEASURES (0 rnum)
RULES ITERATE (10) (
rnum [ITERATION_NUMBER] = ITERATION_NUMBER
);

-- easier, returns 1,1,1... (10 rows), so use rownum to get increasing integers
SELECT ROWNUM FROM (
  SELECT 1 FROM DUAL
  CONNECT BY LEVEL <= 10
);

-- drop in our dates
-- returns 1, 2, 3 ... 31
SELECT ROWNUM FROM (
  SELECT 1 FROM DUAL
  CONNECT BY LEVEL <= (TO_DATE('01-JUN-2008') - TO_DATE('01-MAY-2008'))
);

-- full date set then: (may 1st, to may 31st)
SELECT TO_DATE('01-MAY-2008') + ROWNUM - 1
FROM (
  SELECT ROWNUM FROM (
    SELECT 1 FROM DUAL
    CONNECT BY LEVEL <= (TO_DATE('01-JUN-2008') - TO_DATE('01-MAY-2008'))
  )
);

-- being inclusive of the end-date (june 1st) 
SELECT ROWNUM FROM (
  SELECT 1 FROM DUAL
  CONNECT BY LEVEL <= (TO_DATE('01-JUN-2008') - TO_DATE('01-MAY-2008') + 1)
)