How to add two time values...?

[slicendice]

Hi
I have two variables that are involved in processing some appointment data - one is the start time of the appointment and the other is the duration (in minutes). I need to calculate the end time of the appointment using those two items - which in itself is not a complex concept! I know I can add a number of minutes to a time in SQL using something like:

    SELECT to_char(to_date(10.40, 'HH24.MI') + INTERVAL '30' MINUTE, 'HH24:MI')
    FROM dual;

This works OK in that it gives me 11.10 as the result. However, if I try and build this in PL/SQL I get problems. If I try something like this:

DECLARE
    start_time  VARCHAR2(5) := '10.40';
    end_time    VARCHAR2(5);
    duration    VARCHAR2(2) := '30';

BEGIN
    SELECT to_char(to_date(start_time, 'HH24.MI') + INTERVAL duration MINUTE, 'HH24:MI')
    INTO end_time
    FROM dual;
END;

...I just get an ORA-00907 (missing right parenthesis). Playing around with it seems to imply that it doesn't like the use of the variable "duration" and only works if I hard code a value.

Is it possible to do what I'm trying to do and have a variable as the duration field?

Thanks very much

 

[Dagon]

I'm not sure you can use variables in the INTERVAL stuff. The simple workaround is to change your number of minutes into a fraction of a day and add that e.g.

select sysdate + 30/(24*60) from dual

 

[slicendice]

Yep, I think that's the best/easiest way to do it.

Thanks Dagon