Search code examples
plsqlsystimestamp

how to add HH:MI to systimestamp in PLSQL code with a HH:MI as variable

I want to replace '02:00' with a variable in the below code in PLSQL procedure.

SELECT SYSTIMESTAMP + interval '02:00' HOUR TO MINUTE 
  INTO est_close_date 
FROM DUAL;
Solution

  • Define the variable as the number of hours, then you can do:

    declare
  l_number_of_hours integer;
  ...
begin
 ...
 l_number_of_hours := 6;

 SELECT SYSTIMESTAMP + interval '1' HOUR * l_number_of_hours
   INTO est_close_date 
 FROM DUAL;
end;

    If you need finer granularity, use minutes:

    declare
  l_number_of_minutes integer;
  ...
begin
 ...
 l_number_of_minutes := 2 * 60 + 30;

 SELECT SYSTIMESTAMP + interval '1' minute * l_number_of_minutes
   INTO est_close_date 
 FROM DUAL;
end;