/*Use the function to calculate age between two date*/
CREATE OR REPLACE FUNCTION f_calculate_age (p_birth_date IN DATE,p_compare_date in date)RETURN VARCHAR
IS
p_year NUMBER;
p_month NUMBER;
p_day NUMBER;
p_age VARCHAR2 (200);
/******************************************************************************
NAME: f_calculate_age
PURPOSE: Calculate age
******************************************************************************/
BEGIN
SELECT ABS(TRUNC (MONTHS_BETWEEN (p_compare_date, dob) / 12)) YEAR,
ABS(TRUNC (MOD (MONTHS_BETWEEN (p_compare_date, dob), 12))) MONTH,
ABS(TRUNC ( p_compare_date
- ADD_MONTHS (dob,
TRUNC (MONTHS_BETWEEN (p_compare_date, dob) / 12)
* 12
+ TRUNC (MOD (MONTHS_BETWEEN (p_compare_date, dob),
12)
)
)
)) DAY
INTO p_year,
p_month,
p_day
FROM (SELECT TO_DATE (TO_CHAR (p_birth_date, 'DDMMRRRR'),
'DDMMRRRR') dob
FROM DUAL);
SELECT p_year
|| DECODE (abs(p_year), 1, 'Year ', 0, 'Year ', ' Years ')
|| p_month
|| DECODE (abs(p_month), 1, ' Month ', 0, ' Month ', ' Months ')
|| p_day
|| DECODE (abs(p_day), 1, 'Day.', 0, ' Day.', ' Days.')
INTO p_age
FROM DUAL;
RETURN p_age;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
WHEN OTHERS
THEN
-- Consider logging the error and then re-raise
RAISE;
END f_calculate_age;
/
কোন মন্তব্য নেই:
একটি মন্তব্য পোস্ট করুন