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>
at

6 comments:

  1. AnonymousMarch 08, 2007 4:08 pm

    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

    ReplyDelete
  2. UnknownJuly 03, 2007 1:05 pm

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

    ReplyDelete
  3. AnonymousOctober 31, 2007 2:09 pm

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

    ReplyDelete
  4. GregoryJune 06, 2011 6:38 pm

    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

    ReplyDelete
  5. Tee ChessJuly 09, 2011 12:24 pm

    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

    ReplyDelete
  6. AnonymousDecember 19, 2011 6:12 pm

    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??

    ReplyDelete