Geek Answers Handbook

Oracle sequence?

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

At first I thought of a sequence, but apparently the only type supported is a number, so now I'm looking for an effective way to do this.

hails hoax

+7
date sql oracle plsql sequence
source share
2 answers

If you want to fill out a block of records with consecutive dates, this is fairly easy to do. The following query generates ten dates. All you have to do is adjust the seed date to give you a starting point and level in the connect by clause so that it matches your endpoint, and then connect it to the insert .

 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>
+17
source share

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

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

then you can 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
0
source share

More articles:


All Articles