Difference between two dates as a single column

here is an example:

SQL> select * from t;

END_DATE  START_DAT
--------- ---------
28-APR-07 28-APR-07

SQL> select sysdate - end_date from t;

SYSDATE-END_DATE
----------------
      .133576389

SQL> select trunc( mod( (sysdate - end_date)*24, 24 ) )||':'||trunc( mod( (sysdate - end_date)*24*60, 60 ) ) from t;

TRUNC(MOD((SYSDATE-END_DATE)*24,24))||':'||TRUNC(MOD((SYSDATE-END_DATE)*24*60,60
--------------------------------------------------------------------------------
3:15

SQL>

Hello,

Two improvements on anokun7 suggestion:
1. to get 03:15 instead of 3:15 (and further if the return value was 3hours and 5 minutes anokun7 query would return 3:5 instead of 03:05) you should modify format the return statement as the following:
SELECT    LPAD (TO_CHAR (TRUNC (MOD ((SYSDATE - end_date) * 24, 24))), 2, '0')
       || ':'
       || LPAD (TO_CHAR (TRUNC (MOD ((SYSDATE - end_date) * 24 * 60, 60))), 2, '0')
  FROM t;

2. what anokun7 suggested is correct, if you know that the difference between end_date and sysdate is less than one day, of if you only want the time difference. If not you should cater for the date part as well. You can do that by adding TRUNC(SYSDATE - end_date)||' Days, ' at the begining of the expression:
SELECT   TRUNC(SYSDATE - end_date)||' Days, '|| /* This is the days diff */
 LPAD (TO_CHAR (TRUNC (MOD ((SYSDATE - end_date) * 24, 24))), 2, '0')
       || ':'
       || LPAD (TO_CHAR (TRUNC (MOD ((SYSDATE - end_date) * 24 * 60, 60))), 2, '0')  Time_Diff
  FROM t;

If you just want the time of day differential, then
select lpad(trunc(((sys_date - trunc(sys_date)) - (col3 - trunc(col3))) * 24),2,0)
||':'||lpad(round(mod(((sys_date - trunc(sys_date)) - (col3 - trunc(col3))) * 24,1) * 60, 0),2,0) time_diff
from datetbl;
If you want the total hours and minutes difference between the two dates, then
select lpad(trunc((sys_date - col3) * 24),2,0)
||':'||lpad(round(mod((sys_date - col3) * 24,1) * 60, 0),2,0) time_diff
from datetbl;
Here are the test examples -
SQL> select to_char(col3,'dd-mon-yy hh24:mi')
  2  ,to_char(sys_date,'dd-mon-yy hh24:mi')
  3  from datetbl;

TO_CHAR(COL3,'DD-MON- TO_CHAR(SYS_DATE,'DD-
--------------------- ---------------------
28-apr-07 12:30       29-apr-07 13:45
28-apr-07 12:38       29-apr-07 13:45
SQL> select lpad(trunc(((sys_date - trunc(sys_date)) - (col3 - trunc(col3))) * 24),2,0)
  2  ||':'||lpad(round(mod(((sys_date - trunc(sys_date)) - (col3 - trunc(col3))) * 24,1) * 60, 0),2,0) time_diff
  3  from datetbl;

TIME_DIFF
-------------
01:15
01:07
SQL> select lpad(trunc((sys_date - col3) * 24),2,0)
  2  ||':'||lpad(round(mod((sys_date - col3) * 24,1) * 60, 0),2,0) time_diff
  3  from datetbl;

TIME_DIFF
-------------
25:15
25:07