Oracle age calculation function.

For calculating age for any date and get the return age by years or months or days or by all as a date I make this function by Oracle SQL

---

CREATE OR REPLACE FUNCTION GET_AGE(D1 IN DATE, C1 IN CHAR) RETURN CHAR IS
BEGIN
DECLARE
V_D1 NUMBER;
V_M1 NUMBER;
V_Y1 NUMBER;
V_D2 NUMBER;
V_M2 NUMBER;
V_Y2 NUMBER;
BEGIN
V_Y1 := TRUNC (MONTHS_BETWEEN(SYSDATE,D1) /12);
V_M1 := TRUNC (MONTHS_BETWEEN(SYSDATE,D1));
V_D1 := TRUNC (SYSDATE - D1);
V_Y2 := TRUNC (MONTHS_BETWEEN(SYSDATE,D1) /12);
V_M2 := MOD (TRUNC (MONTHS_BETWEEN(SYSDATE,D1)),12);
V_D2 := MOD (MOD (TRUNC (SYSDATE - D1) , 365) , 30);
CASE
   WHEN UPPER(C1) ='D' THEN RETURN V_D1;
   WHEN UPPER(C1) ='M' THEN RETURN V_M1;
   WHEN UPPER(C1) ='Y' THEN RETURN V_Y1;
   ELSE RETURN V_D2||'/'||V_M2||'/'||V_Y2;
END CASE;

EXCEPTION
WHEN NO_DATA_FOUND THEN
   NULL;
WHEN OTHERS THEN
   RAISE;
END;
END GET_AGE;

---

How to use this function :
call the function using two parameters,
first parameter (DATE) as the target date to calculate the age for it,
second parameter (CHAR) I use it to change the return value format like this:
'Y' => calculate the age only by years.
'M' => calculate the age only by months.
'D' => calculate the age only by days.
'' => calculate the age in this format (DAYS/MONTHS/YEARS).
examples:

-- CALCULATE AGE AS RETURN VALUE DAYS/MONTHS/YEARS
SELECT GET_AGE(TO_DATE('10/05/1990','DD/MM/YYYY'),'') FROM DUAL;
RETURN VALUE> 10/7/20

-- CALCULATE AGE BY YEARS
SELECT GET_AGE(TO_DATE('10/05/1990','DD/MM/YYYY'),'Y') FROM DUAL;
RETURN VALUE> 20

-- CALCULATE AGE BY MONTHS
SELECT GET_AGE(TO_DATE('10/05/1990','DD/MM/YYYY'),'M') FROM DUAL;
RETURN VALUE> 247

-- CALCULATE AGE BY DAYS
SELECT GET_AGE(TO_DATE('10/05/1990','DD/MM/YYYY'),'D') FROM DUAL;
RETURN VALUE> 7520

If this post was good and helpful for you, Please give it Like.

.