Wednesday, August 15, 2012

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 
       l_result    number; 
       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; 

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!

No comments: