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;
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:
Post a Comment