How can i pass variable with in single quote oracle sql

user_12837338 0
Viewed 183
select
to_char(sysdate + interval '2' hour,'hh12:mi AM') as Time
from dual

i have a query that will add 2 hours from current system time , but it may add aur subtract the time and also hour's value will also be dynamic so i have to use operator value it may + or - and similarly hour value it may 2 ,3 ,4 or 5 so my query will be

select
to_char(sysdate :operator interval ':hourvalue' hour,'hh12:mi AM') as Time
from dual

it gives me error

ORA-00907: missing right parenthesis

please help me out i am using oracle 11g

oracle11g oracle sql
3 Answers

You cannot have an INTERVAL literal of a variable amount; however, you can have a fixed INTERVAL literal and then multiply it by a bind variable:

SELECT TO_CHAR(
         SYSDATE + :hourvalue * INTERVAL '1' HOUR,
         'hh12:mi AM'
       ) AS TIME
FROM   DUAL

If you want a negative amount then just specify a negative :hourvalue rather than using a separate :operator bind variable.

user_15092640

You can use NUMTODSINTERVAL(n, 'hour') where n can has a negative value as well. For example

  SELECT NUMTODSINTERVAL((-1)*10, 'hour') h FROM DUAL;

You can also create an interval -(1 hour 37 min 41 sec):

  SELECT NUMTODSINTERVAL((-1) * (1*3600 + 37*60 + 41), 'second') hms FROM DUAL;

Note, n is a number (decimal), so this will work too:

  SELECT NUMTODSINTERVAL((-1) * (1 + 37/60 + 41/3600), 'hour') hms FROM DUAL;
user_62199790

It's only possible to use bind variables for arguments, you cannot use operators as bind variables. A workaround is to multiply the interval by -1 for negative and 1 for positive.

SELECT 
  TO_CHAR(
    SYSDATE + numtodsinterval((CASE WHEN :operator = '-' THEN -1 ELSE 1 END * :interval),'hour'),
    'hh12:mi AM'
    ) as time
FROM DUAL;
user_41898140
Related