oracle date sequence?

Question

Hi

I have an oracle db and I need a table containing all the dates spanning 2 years; for example from 01/01/2011 to 01/01/2013.

First I thought of a sequence but apparently the only supported type is number, so now I am looking for an efficient way to do this

cheers hoax

Answer 1

If what you want is to populate a block of records with sequential dates, that is easy enough to do. The following query generates ten dates. All you need to do is adjust the seed date to give you your starting point and the level in the connect by clause to fit your end point, and then plug it into an insert statement.

SQL> select (trunc(sysdate, 'MM')-1) + level
  2  from dual
  3  connect by level <= 10
  4  /

(TRUNC(SY
---------
01-JAN-10
02-JAN-10
03-JAN-10
04-JAN-10
05-JAN-10
06-JAN-10
07-JAN-10
08-JAN-10
09-JAN-10
10-JAN-10

10 rows selected.

SQL>

Answer 2

Say for example we have a table named: datums, with the column datum(date type) table contains:

21-01-2010
22-01-2010
01-12-2009
06-10-2008
03-07-2007

then you could use:

SELECT * 
  FROM datums 
 WHERE datum 
 BETWEEN to_date('01/01/2009','mm/dd/yyyy') 
     AND to_date('12/31/2010','mm/dd/yyyy')

result:

21-01-2010
22-01-2010
01-12-2009