adding dates
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: adding dates

  1. #1
    Join Date
    Jul 2001
    Posts
    181

    adding dates

    Hi,

    I have a table in which I want to input.

    I have a table:

    desc Calender
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    DATE DATE
    JOB_SEq NUMBER(10)

    The data in the DATE column is the same as below
    05-JAN-03
    06-JAN-03

    The problem is I want to insert data for the whole of 2003, My question is, is there a simple pl/sql loop I can write without having to write numerous insert statements to get data ranging from
    05-JAn-03 - 31-DEc-03.


    Thanks in advance...

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Code:
    INSERT INTO calender ("DATE") 
    SELECT TO_DATE('05-JAN-2003','DD-MON-YYYY')+ROWNUM-1
    FROM ALL_OBJECTS
    WHERE TO_DATE('05-JAN-2003','DD-MON-YYYY')+ROWNUM-1 <= TO_DATE('31-DEC-2003','DD-MON-YYYY');
    COMMIT;
    And BTW, rename your column "DATE" to something else - DATE is a reserved word and you'll get into troubles sooner or later with the column named like this....
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588
    Is this what you what?
    Code:
    SQL> SELECT TO_Date('05-JAN-03','DD-MON-YY') + ROWNUM Dates FROM
      2  all_objects WHERE ROWNUM  < 361;
    
    Dates
    --------
    06.01.03
    07.01.03
    08.01.03
    09.01.03
    10.01.03
    11.01.03
    :
    :
    :
    :
    24.12.03
    25.12.03
    26.12.03
    27.12.03
    28.12.03
    29.12.03
    30.12.03
    31.12.03

  4. #4
    Join Date
    Jul 2001
    Posts
    181
    Yes!

  5. #5
    Join Date
    Feb 2001
    Location
    UAE
    Posts
    304
    FYI you can add numbers to date.

    sql>select sysdate from dual;

    SYSDATE
    ---------
    06-JAN-03

    sql>select sysdate+1 from dual;

    SYSDATE+1
    ---------
    07-JAN-03
    Agasimani
    OCP(10g/9i/8i/8)

  6. #6
    Join Date
    Jul 2001
    Posts
    181
    Thanks jmodic your insert worked fine...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width