| current_date + interval 'x' question [message #8032] |
Tue, 22 July 2003 18:40  |
Paul
Messages: 164
Registered: April 1999
|
Senior Member |
|
|
I'm looking to combine the following two queries into a single query.
select current_date + interval 'x' hour from dual;
The value 'x' is dynamic and comes from a query like:
select hours_to_wait from priority where priority_id = 3;
So the final query may look something like this:
select current_date + interval '(select num_hours from priority where priority_id = 3)' hour from dual;
I cant seem to get the single quotes/concatenation to work correctly. I get errors like:
ORA-30089: missing or invalid <datetime field>
ORA-01867: the interval is invalid
etc...
BTW, I'm using Oracle 9i but dont want to use to_dsinterval because that function requires the hours to be between 0 and 23.
|
|
|
|
| Re: current_date + interval 'x' question [message #8052 is a reply to message #8032] |
Wed, 23 July 2003 11:20  |
sverch
Messages: 582
Registered: December 2000
|
Senior Member |
|
|
You need internal view. Like this:
1 SELECT sysdate+a.hrs FROM dual,
2* (SELECT rownum hrs FROM customer WHERE ROWNUM<2) a
SQL> /
SYSDATE+A.H
-----------
24-JUL-2003
So your query will be something like this:
select sysdate + a.interval FROM
(SELECT num_hours INTERVAL FROM priority WHERE priority_id = 3)a FROM dual;
But I don't know what you actually adding to sysdate,
hours or days. You have to think about it because date+1 adds one day, date+2 adds 2 daye etc. and if you have hours you have to do some math.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
