Adsense Ad

Wednesday 15 March 2017

Function to find years month days hours mins sec from date

CREATE OR REPLACE FUNCTION GET_RRRRMMDDHHMI(P_DATE1 DATE, ---FROM DATE
                                               P_DATE2 DATE, --TO DATE
                                               P_TYPE  VARCHAR2)
  RETURN CHAR IS

  YEARS        NUMBER;
  MONTHS       NUMBER;
  DAYS         NUMBER;
  DAY_FRACTION NUMBER;
  HRS          NUMBER;
  MINTS        NUMBER;
  SEC          NUMBER;

BEGIN

  YEARS  := TRUNC(MONTHS_BETWEEN(P_DATE2, P_DATE1) / 12);
  MONTHS := MOD(TRUNC(MONTHS_BETWEEN(P_DATE2, P_DATE1)), 12);
  DAYS   := TRUNC(P_DATE2 -
                  ADD_MONTHS(P_DATE1,
                             TRUNC(MONTHS_BETWEEN(P_DATE2, P_DATE1))));

  DAY_FRACTION := (P_DATE2 - P_DATE1) - TRUNC(P_DATE2 - P_DATE1);

  HRS   := TRUNC(DAY_FRACTION * 24);
  MINTS := TRUNC((((DAY_FRACTION) * 24) - (HRS)) * 60);
  SEC   := TRUNC(MOD((P_DATE2 - P_DATE1) * 86400, 60));
  IF P_TYPE = 'YY' THEN
    RETURN(YEARS || ' Years ');
  ELSIF P_TYPE = 'MM' THEN
    RETURN(MONTHS || ' Months ');
  ELSIF P_TYPE = 'DD' THEN
    RETURN(DAYS || ' Days ');
  ELSIF P_TYPE = 'YMD' THEN
    RETURN(YEARS || ' Years ' || MONTHS || ' Months ' || DAYS || ' Days ');
  ELSIF P_TYPE = 'ALL' THEN
    RETURN(YEARS || ' Years ' || MONTHS || ' Months ' || DAYS || ' Days ' || HRS ||
           ' Hours ' || MINTS || ' Minutes ' || SEC || ' Seconds');
  END IF;

END;

No comments: