Tuesday, December 5

Date range generation in Oracle

Another interesting question someone asked in the IRC #oracle chat.
-How do I create a table with all dates from 2000 until today in it?
It's rather simple in plain SQL, no PL/SQL or scripting required.
SQL> create table date1 (d date);

Table created.

SQL> insert
into date1(d)
select
to_date('00-01-01','YY-DD-MM') + level
from
dual
where
(to_date('00-01-01','YY-DD-MM')+level) < sysdate
connect
by level<=1000000
/

2530 rows created.

SQL> select min(d) s, max(d) e from date1;

S E
--------- ---------
02-JAN-00 05-DEC-06

SQL>

6 comments:

Anonymous said...

Dear Sir,
I have tried the following query but only one date produced...kindly guid me ...


SQL> select to_date('00-01-01','YY-DD-MM') + level from dual where (to_date('00-01-01','YY-DD-MM')+level) < sysdate connect by level<=1000000;

TO_DATE('
---------
02-JAN-00

Unknown said...

SELECT TO_DATE('01-02-2003','DD-MM-YYYY')+ROWNUM FROM ( SELECT 1 nothing FROM DUAL CONNECT BY LEVEL <= 1000 )

Anonymous said...

Is it possible to form a query that will return all of these dates without creating a temporary table?

Gregory said...

Hi,
It's very useful! =)
How could I generate these for monthes/year only?
Something like:
period: 01/2000 (MM/YYYY) to Sysdate
Expected return:
1 02/2000
2 03/2000
3 04/2000
4 05/2000
5 ...
6 06/2011

Thanks in advance

Tee Chess said...

Interesting. I did implemented this query and didn't got the desired results but what actually happened was my system totally got hanged up. Then I realized what I did wrong was that instead of using < I put > in my query. I also wanted to know the same that Carlos has requested for.
sap upgrade testing

Anonymous said...

hi there I am having trouble wit this part:
SQL> insert
into date1(d)
select
to_date safemeds ('00-01-01','YY-DD-MM') + level
from
dual
where
(to_date('00-01-01','YY-DD-MM')+level) <

can you tell me what I am doing wrong please??