Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Adding a interval to a date

Marius2Jun 6 2008 — edited Jun 6 2008
I have the number of day and months that I want to add to a date in a number variable, how do I add it or how do I transform a number into an interval of that many months?

I have tried a few things without success:

Works with literals
SQL> select sysdate + interval '5' day from dual;

SYSDATE+IN
----------
11.06.2008

but not with variables
SQL> declare
2 mnd number;
3 begin
4 mnd := 5;
5
6 select sysdate + interval 'mnd' day from dual;
7 end;
8 /
select sysdate + interval 'mnd' day from dual;
*
ERROR at line 6:
ORA-06550: line 6, column 29:
PL/SQL: ORA-01867: the interval is invalid
ORA-06550: line 6, column 3:
PL/SQL: SQL Statement ignored


SQL> declare
2 mnd number;
3 begin
4 mnd := 5;
5
6 select sysdate + interval mnd day from dual;
7 end;
8 /
select sysdate + interval mnd day from dual;
*
ERROR at line 6:
ORA-06550: line 6, column 33:
PL/SQL: ORA-00923: FROM keyword not found where expected
ORA-06550: line 6, column 3:
PL/SQL: SQL Statement ignored


Anyone able to give me a pointer?
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 4 2008
Added on Jun 6 2008
6 comments
827 views