Getting the difference between Dates

For example:

SQL> set serveroutput on
SQL>
SQL> declare
  2          a       date;
  3          b       date;
  4  begin
  5          a := sysdate;
  6          dbms_lock.sleep(10);    -- sleep about 10 seconds give or take
  7          b := sysdate;
  8
  9          dbms_output.put_line( b-a || ' of a day has elapsed' );
 10          dbms_output.put_line( (b-a)*24 || ' of an hour has elapsed' );
 11          dbms_output.put_line( (b-a)*24*60 || ' of a minute has elapsed' );
 12          dbms_output.put_line( (b-a)*24*60*60 || ' seconds has elapsed' );
 13  end;
 14  /
.000127314814814814814814814814814814814815 of a day has elapsed
.00305555555555555555555555555555555555556 of an hour has elapsed
.1833333333333333333333333333333333333336 of a minute has elapsed
11.00000000000000000000000000000000000002 seconds has elapsed

PL/SQL procedure successfully completed.

To break the diff between 2 dates into days, hours, minutes, sec -- you can use the following:

select to_char( created, 'dd-mon-yyyy hh24:mi:ss' ),
       trunc( sysdate-created ) "Dy",
       trunc( mod( (sysdate-created)*24, 24 ) )  "Hr",
       trunc( mod( (sysdate-created)*24*60, 60 ) )  "Mi",
 trunc( mod( (sysdate-created)*24*60*60, 60 ) ) "Sec",
       to_char( sysdate, 'dd-mon-yyyy hh24:mi:ss' ),
       sysdate-created "Tdy",
       (sysdate-created)*24 "Thr",
       (sysdate-created)*24*60 "Tmi",
       (sysdate-created)*24*60*60 "Tsec"
from all_users
where rownum < 50
/

Dy gives you number of days between 2 dates (partial days discarded).  Tdy gives you total days including fractions (eg: you'll get 1.5 for 1 and 1/2 days).  Likewise for HR and THR and so on.

Sybase users are used to using datediff in the database and are many times baffled by the lack of a function to do date arithemetic that they assume Oracle cannot do it.  It is really just that date arithmetic is so trivial that a specialized function like datediff is not needed.  Just subtract.  You get the difference in days.  Multiply by 24 -- hours, multiply by 60 minutes, multiply by 60 -- seconds.

If you really want 'datediff' in your database, you can just do something like this:
 

SQL> create or replace function datediff( p_what in varchar2,
  2                                       p_d1   in date,
  3                                       p_d2   in date ) return number
  4  as
  5      l_result    number;
  6  begin
  7      select (p_d2-p_d1) *
  8             decode( upper(p_what),
  9                     'SS', 24*60*60, 'MI', 24*60, 'HH', 24, NULL )
 10       into l_result from dual;
 11
 11      return l_result;
 12  end;
 13  /

Function created

Now, i just create a view to demonstrate with:
 

SQL> create or replace view temp_view
  2  as
  3  select to_date('01-JAN-1999 12:02:04', 'dd-mon-yyyy hh24:mi:ss' ) d1,
  4         to_date('15-MAR-1999 01:34:23', 'dd-mon-yyyy hh24:mi:ss' ) d2
  5  from dual
  6  /
View created.
 

SQL> select datediff( 'ss', d1, d2 ) seconds from temp_view;

   SECONDS
----------
   6269539

SQL> select datediff( 'mi', d1, d2 ) minutes from temp_view;

   MINUTES
----------
104492.317

SQL> select datediff( 'hh', d1, d2 ) hours from temp_view;

     HOURS
----------
1741.53861