Sep 9, 2012

How to get the difference between two dates?

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;

No comments:

OraApps Search

Custom Search

Search This Blog