Dates function

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

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:
[email protected]> 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'))
[email protected]> /

1 row created.

[email protected]> commit;

Commit complete.

[email protected]> select d2 - d1 from date_test;\
2
[email protected]> select d2 - d1 from date_test;

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

[email protected]> select d4 - d2 from date_test;

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

[email protected]> select d4 - d1 from date_Test;

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

[email protected]> select sysdate - d1 from date_test;

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

[email protected]>

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

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

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