Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

How to add days, hours, minutes and seconds to a date?

user639304Jun 29 2011 — edited Jul 8 2011
Hi,

I have the following problem.
Given 4 integers D,H,M and S (each of them can be negative) and a date Da, I want to add D days, H hours, M minutes and S seconds to the date Da.
For example, if 
Da= to_date('28/06/2011 14:50:35','dd/mm/yyyy HH24:mi:ss'), and D = 3, H = -2, M = 20 and S = -12,
it means I want to add 3 days, -2 hours, 20 minutes and -12 seconds to the date Da, and the new date must be the following date: 
to_date('01/07/2011 13:10:23','dd/mm/yyyy HH24:mi:ss')
Is it possible to write a query to solve this problem or should I use PL/SQL?

Thanks.
This post has been answered by 6363 on Jun 29 2011
Jump to Answer

Comments

6363
Answer
There is no need for PL/SQL
SQL> alter session set nls_date_format = 'DD/MM/YYYY HH24:MI:SS';

Session altered.

SQL> var d number
SQL> var h number
SQL> var m number
SQL> var s number
SQL> exec :d := 3; :h := -2; :m := 20; :s := -12

PL/SQL procedure successfully completed.

SQL> select to_date('28/06/2011 14:50:35','dd/mm/yyyy HH24:mi:ss')
  2     + :d
  3     + (:h / 24)
  4     + (:m / 24 / 60)
  5     + (:s / 24 / 60 / 60)
  6  from dual;

TO_DATE('28/06/2011
-------------------
01/07/2011 13:10:23
Marked as Answer by user639304 · Sep 27 2020
user639304
Hi,

Thanks. You solved my problem.
_
Another way would be: -
SELECT TO_DATE('28/06/2011 14:50:35', 'DD/MM/YYYY HH24:MI:SS')
       + NUMTODSINTERVAL(  3, 'Day')
       + NUMTODSINTERVAL( -2, 'Hour')
       + NUMTODSINTERVAL( 20, 'Minute')
       + NUMTODSINTERVAL(-12, 'Second')
FROM dual;
1 - 3
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Aug 5 2011
Added on Jun 29 2011
3 comments
11,055 views