-
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...
-
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....
-
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
-
-
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
-
Thanks jmodic your insert worked fine...
