Saturday, May 25, 2013

datetime_difference Function in Minutes in Oracle



Execute the Function

CREATE OR REPLACE Function datetime_difference(p_arr_datetime in date,p_dep_datetime in date) return NUMBER is
   v_time_difference Number:=0;
Begin
   --Select  To_Number(To_date(To_Char(p_arr_datetime,'dd/Mon/yyyy hh24mi'))-To_date(To_Char(p_dep_datetime,'dd/Mon/yyyy hh24mi'))) INTO v_time_difference From Dual;
   --Select  p_arr_datetime - p_dep_datetime INTO v_time_difference From Dual;
   If p_arr_datetime IS NULL OR p_dep_datetime IS NULL Then
    return 0;
   End If;
   If p_arr_datetime is not null and p_dep_datetime is not null then
   Select  To_Number(to_date(p_arr_datetime,'DD/MON/YYYY HH24MI')-to_date(p_dep_datetime,'DD/MON/YYYY HH24MI')) de INTO v_time_difference  From Dual;
  -- SELECT DATE_DIFF(p_arr_datetime, p_dep_datetime) de INTO v_time_difference FROM DUAL;
   return v_time_difference;
   End If;
End;
/


Execute the Query 
 

select datetime_difference(TO_DATE('01/MAR/2013 1220','dd/Mon/yyyy hh24mi'),TO_DATE('01/MAR/2013 1200','dd/Mon/yyyy hh24mi')) from dual;

No comments:

Post a Comment