PL-SQL difference of dates in seconds

this expression, as a difference of 2 dates:

TO_DATE('2012-08-13 11.20.00', 'YYYY-MM-DD HH24:MI:SS') - max(CREATIONTIME)

is a "INTERVAL DAY TO SECOND", not a "number".


This function provided by asktom

create or replace function datediff( p_what in varchar2, 
                                        p_d1   in date, 
                                        p_d2   in date ) return number 
   as 
       l_result    number; 
   begin 
       select (p_d2-p_d1) * 
              decode( upper(p_what), 
                      'SS', 24*60*60, 'MI', 24*60, 'HH', 24, NULL ) 
        into l_result from dual; 
  
       return l_result; 
   end; 
 

is very useful, you can do this:


select trunc(datediff('ss', TO_DATE('2012-08-13 11.20.00', 'YYYY-MM-DD HH24:MI:SS'), max(A.CREATIONTIME) ))

and you get the result as a numeric value!