Dates function

[badrinathn]

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
Badrinath

[marist89]

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>

[rsuri]

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

[badrinathn]

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)
Badrinath

[rsuri]

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