Is it possible to write a query which returns a date for every day between two specified days?

Question

Basically, the question says it all. I need a PL\SQL query that returns a list of dates between two dates such that for 01-JAN-2010 to 20-JAN-2010 I would get 20 rows returned:

the_date 
--------
01-JAN-2010
02-JAN-2010
03-JAN-2010
04-JAN-2010
...
20-JAN-2010

Answer 1

No. Queries can only return existing data - and if you have no table of all days, you are out.

That said (I am no oracle specialist), a function or stored procedure should be able to do that. In SQL Server I would have a function returning a table (that I could then use in joins).

But a pure query - no. Not unless oracle has such a function already.

Answer 2

Here's an example from postgres, I hope the dialects are comparable in regards to recursive

WITH RECURSIVE t(n) AS (
    VALUES (1)
  UNION ALL
    SELECT n+1 FROM t WHERE n < 20
)
SELECT n FROM t;

...will return 20 records, numbers from 1 to 20 Cast/convert these to dates and there you are

UPDATE: Sorry, don't have ORA here, but according to this article

SELECT
   SYS_CONNECT_BY_PATH(DUMMY, '/')
FROM
   DUAL
CONNECT BY
   LEVEL<4;

gives

SYS_CONNECT_BY_PATH(DUMMY,'/')
--------------------------------
/X
/X/X
/X/X/X

It is also stated that this is supposed to be very efficient way to generate rows. If ROWNUM can be used in the above select and if variable can be used in LEVEL condition then solution can be worked out.

UPDATE2:

And indeed there are several options.

SELECT (CAST('01-JAN-2010' AS DATE) + (ROWNUM - 1)) n
FROM   ( SELECT 1 just_a_column
         FROM   dual
         CONNECT BY LEVEL <= 20
       )

orafaq states that: 'It should be noted that in later versions of oracle, at least as far back as 10gR1, operations against dual are optimized such that they require no logical or physical I/O operations. This makes them quite fast.', so I would say this is not completely esoteric.

Answer 3

OK, so it might seem a little hacky, but here's what I've come up with:

SELECT (CAST('01-JAN-2010' AS DATE) + (ROWNUM - 1)) AS the_date
FROM all_objects
WHERE ROWNUM <= CAST('20-JAN-2010' AS DATE) - CAST('01-JAN-2010' AS DATE) + 1

The magic sauce is using ROWNUM as a seed for date arithmetic, I'm using all_objects but you could use any table that has enough rows in it to supply the required range. You can shuffle it around to make it work off SYSDATE instead of hard coding the value, but in principle I think that the idea is sound.

Here's an example that returns a list of dates from 10 days ago to 10 days time:

SELECT (SYSDATE -10 + (ROWNUM-1)) AS the_date
FROM all_objects
WHERE ROWNUM <= (SYSDATE +10) - (SYSDATE -10) + 1

Answer 4

The following query will return each day between 1/1 and 1/20 (inclusive).

    select to_date('1/1/2010','mm/dd/yyyy')+level
      from dual
connect by level <= to_date('1/20/2010','mm/dd/yyyy')
                    - to_date('1/1/2010','mm/dd/yyyy');