How to get the difference between two dates?
For this we need to develop one Function ::
FUNCTION f_date_difference(
p_start_datetime IN DATE,
p_end_datetime IN DATE )
RETURN
NUMBER
IS
--
-- This Function returns the difference between two dates as:
-- the number of days when P_UNIT = 'D' or
-- the number of hours when P_UNIT = 'H'.
-- Otherwise, it returns 0 ...
--
v_days INTEGER;
v_difference NUMBER(9,2);
v_end_minutes INTEGER;
v_minutes INTEGER;
v_start_minutes INTEGER;
--
BEGIN
v_difference := 0;
--
v_days := ( To_Number( To_Char(p_end_datetime, 'J') ) -
To_Number( To_Char(p_start_datetime, 'J') ) );
--
-- IF ( p_unit = 'D' )
-- THEN
-- v_difference := v_days;
-- END IF;
--
v_end_minutes := ( ( To_Number( To_Char(p_end_datetime, 'HH24' ) ) * 60 ) +
To_Number( To_Char(p_end_datetime, 'MI' ) ) );
--
v_start_minutes := ( ( To_Number( To_Char(p_start_datetime, 'HH24') ) * 60 ) +
To_Number( To_Char(p_start_datetime, 'MI' ) ) );
--
v_minutes := ( ( v_days * 24 * 60 ) + ( v_end_minutes - v_start_minutes ) );
--
-- v_difference := mod(( v_minutes / 60 ),2);
v_difference := ( v_minutes / 60 ) ;
--
--fnd_MESSAGE.set_string('timecalcltd..'|| round(v_difference,2)); fnd_message.show;
RETURN round(v_difference,2);
--
EXCEPTION
WHEN OTHERS THEN
--fnd_MESSAGE.set_string('resource txn interface error..'||SQLERRM); fnd_message.show;
raise form_trigger_failure;
--
END f_date_difference;
For this we need to develop one Function ::
FUNCTION f_date_difference(
p_start_datetime IN DATE,
p_end_datetime IN DATE )
RETURN
NUMBER
IS
--
-- This Function returns the difference between two dates as:
-- the number of days when P_UNIT = 'D' or
-- the number of hours when P_UNIT = 'H'.
-- Otherwise, it returns 0 ...
--
v_days INTEGER;
v_difference NUMBER(9,2);
v_end_minutes INTEGER;
v_minutes INTEGER;
v_start_minutes INTEGER;
--
BEGIN
v_difference := 0;
--
v_days := ( To_Number( To_Char(p_end_datetime, 'J') ) -
To_Number( To_Char(p_start_datetime, 'J') ) );
--
-- IF ( p_unit = 'D' )
-- THEN
-- v_difference := v_days;
-- END IF;
--
v_end_minutes := ( ( To_Number( To_Char(p_end_datetime, 'HH24' ) ) * 60 ) +
To_Number( To_Char(p_end_datetime, 'MI' ) ) );
--
v_start_minutes := ( ( To_Number( To_Char(p_start_datetime, 'HH24') ) * 60 ) +
To_Number( To_Char(p_start_datetime, 'MI' ) ) );
--
v_minutes := ( ( v_days * 24 * 60 ) + ( v_end_minutes - v_start_minutes ) );
--
-- v_difference := mod(( v_minutes / 60 ),2);
v_difference := ( v_minutes / 60 ) ;
--
--fnd_MESSAGE.set_string('timecalcltd..'|| round(v_difference,2)); fnd_message.show;
RETURN round(v_difference,2);
--
EXCEPTION
WHEN OTHERS THEN
--fnd_MESSAGE.set_string('resource txn interface error..'||SQLERRM); fnd_message.show;
raise form_trigger_failure;
--
END f_date_difference;
No comments:
Post a Comment