Is it possible to write a query that returns a date for each 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, for example, for 01-JAN-2010 to 20-JAN-2010, I would get 20 rows:

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

Answer 1

Here is an example from postgres, I hope dialects are comparable with respect 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 Send / convert them to dates and there you are

UPDATE: Sorry, there is no ORA, 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 pointed out that this should be a very efficient way of generating strings. If ROWNUM can be used in the above selection, and if the variable can be used in LEVEL state, then a solution can be developed.

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 the oracle, at least another 10gR1, operations against dual are optimized so that they do not require any logical or physical I / O operations. This makes them pretty fast”, therefore I would say that this is not entirely esoteric.

Answer 2

The next request will be returned every 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'); 

Answer 3

Ok, so this may seem a bit hacky, but here is what I came 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 uses ROWNUM as the seed for date arithmetic, I use all_objects , but you can use any table that has enough rows to provide the required range. You can shuffle it to make it work SYSDATE instead of hard-coding the value, but basically, I think the idea is sound.

Here is an example that returns a list of dates from 10 days ago to 10 days:

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

Answer 4

Not. Queries can only return existing data - and if you do not have a table of all days, you are absent.

However (I am not an oracle specialist), a function or stored procedure should be able to do this. In SQL Server, I would have a function that returns a table (which I could use in joins).

But a clean request is not. If only the oracle does not have such a function already.