CODEWITHSUNDEEP
datetimeplsqloracle11g
Privesh
Privesh

Reputation: 657

Adding hours after midnight today

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

Upvotes: 0

Views: 94

Answers (2)

MT0
MT0

Reputation: 167822

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

Upvotes: 1

Koen Lostrie
Koen Lostrie

Reputation: 18630

Trunc SYSDATE to get midnight. Then add hours: 1 hour is 1/24 day so you'd add the number of hours divided by 24. Example.

koen>DECLARE
  2    l_date DATE;
  3    l_hours NUMBER :=  27;
  4  BEGIN
  5    l_date := TRUNC(SYSDATE) + 27/24;
  6    dbms_output.put_line('l_date is: '||TO_CHAR(l_date,'DD-MON-YYYY HH24:MI'));
  7  END;
  8* /
l_date is: 23-NOV-2022 03:00


PL/SQL procedure successfully completed.

koen>

The select from dual is not advised, you can just assign a variable in pl/sql using the assignment operator :=. The select from dual requires an additional context switch (invoke the sql engine from within pl/sql).

Upvotes: 1

Related Questions