Adding hours after midnight today

Question

Im trying to add hours to midnight of today eg: like 27 hours

I have tried various methods from the internet but am getting the trunc of the dated expected. eg 23-nov-2022 not 23-nov-2022 03:00. when i run it outside my pl/sql procedure/block i get the desired output

the select:

select to_char(to_date(sysdate,'DD-MON-RRRR HH:MI')+hours/24,'DD-MON-RRRR HH:MI') into v_from from dual;

I need some expert assistance

Answer 1

Add an INTERVAL DAY TO SECOND data type to SYSDATE TRUNCated back to midnight:

DECLARE
  v_from DATE;
BEGIN
  SELECT TRUNC(sysdate) + INTERVAL '27' HOUR
  INTO   v_from
  FROM   DUAL;
  DBMS_OUTPUT.PUT_LINE(TO_CHAR(v_from,'DD-MON-RRRR HH:MI'));
END;
/

or, more simply:

DECLARE
  v_from DATE;
BEGIN
  v_from := TRUNC(sysdate) + INTERVAL '27' HOUR;
  DBMS_OUTPUT.PUT_LINE(TO_CHAR(v_from,'DD-MON-RRRR HH:MI'));
END;
/

or, dynamically, with the NUMTODSINTERVAL function:

DECLARE
  v_from  DATE;
  v_hours NUMBER(3,0) := 27;
BEGIN
  v_from := TRUNC(sysdate) + NUMTODSINTERVAL(v_hours,'HOUR');
  DBMS_OUTPUT.PUT_LINE(TO_CHAR(v_from,'DD-MON-RRRR HH:MI'));
END;
/

fiddle