Join Date
Jan 2001
Posts
642
hi,
I have to find the exact time elapsed between the 2 different dates like number of days, hours,minutes and seconds.
Can some one help me

2. Use simple date subtraction. This will give you the number of days between the two dates. You can then use your math skills to figure out how many hours, minutes, seconds the fractional part is. Example:
jeff@dev815nt.us> create table date_test (d1 date, d2 date, d3 date, d4 date);

Table created.
1 insert into date_test values ( to_date('01/12/2001','mm/dd/yyyy'),
2 to_date('01/13/2001 12:21:21','mm/dd/yyyy hh24:mi:ss'),
3 to_date('01/13/2001 19:32:21','mm/dd/yyyy hh24:mi:ss'),
4* to_date('01/15/2001 04:01:21','mm/dd/yyyy hh24:mi:ss'))
jeff@dev815nt.us> /

1 row created.

jeff@dev815nt.us> commit;

Commit complete.

jeff@dev815nt.us> select d2 - d1 from date_test;\
2
jeff@dev815nt.us> select d2 - d1 from date_test;

D2-D1
----------
1.51482639

jeff@dev815nt.us> select d4 - d2 from date_test;

D4-D2
----------
1.65277778

jeff@dev815nt.us> select d4 - d1 from date_Test;

D4-D1
----------
3.16760417

jeff@dev815nt.us> select sysdate - d1 from date_test;

SYSDATE-D1
----------
21.5007639

jeff@dev815nt.us>

3. Senior Member
Join Date
Oct 2000
Location
Dallas:TX:USA
Posts
407
select date1 - date2 from dual;

The above will give you number of days (it could be fractional)

Use TRUNC to get the number of days

Use MOD to get the number of hours, minutes, seconds etc.

Let me know if you need more explnation ?

- Rajeev

Join Date
Jan 2001
Posts
642
RSURI,
CAN YOU GIVE ME THE SQL IF POSSIBLE, or should I compute the hours , minutes and seconds based on the difference between dates using some plsql(For math computations)

5. Senior Member
Join Date
Oct 2000
Location
Dallas:TX:USA
Posts
407
Yes, it will be much easier to compute the data difference and then use PL/SQL to compute the rest of the stuff in a PL/SQL block rather than writing one SELECT statement.
But just for the fun of it I did write a little select sql here --

select TRUNC(3.23) as Days , TRUNC((3.23 - TRUNC(3.23))*24) as Hours, TRUNC((((3.23 - TRUNC(3.23))*24) - TRUNC((3.23 - TRUNC(3.23))*24))*60) as Minutes,
((((3.23 - TRUNC(3.23))*24) - TRUNC((3.23 - TRUNC(3.23))*24))*60 - TRUNC((((3.23 - TRUNC(3.23))*24) - TRUNC((3.23 - TRUNC(3.23))*24))*60))*60 as Seconds from dual

It shows the following result --

DAYS HOURS MINUTES SECONDS
--------- --------- --------- ---------
3 5 31 12

I am sure there will be better ways to accomplish this; this is one of them. I used "3.23" days just an example; In your case you need to replace this with "date1 - date2" or whatever your fields are.

- Rajeev

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•