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;
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment