Posted on 2005-04-28
I have a function to calculate the age of a person based on their DOB and a fixed date for a specified year e.g. 01-Aug- || year
The problem I am having is the function works for a single record e.g.
select fn_AGE_AT_START('28/03/1950', '01/01/2000', '11') from dual
However when I use the function as part of a query it returns 1954, as with all the records e.g. they are all prefixed with 19 and then their age.
the function is
CREATE OR REPLACE FUNCTION fn_AGE_AT_START (DOB varchar2, QSTART varchar2, FY varchar2) RETURN varchar2 is
v_AGE_AT_START := 0;
v_YEAR := 0;
-- ****** Calculate current years *******
IF FY = 10 THEN v_YEAR := 2003; END IF;
IF FY = 11 THEN v_YEAR := 2004; END IF;
IF FY = 12 THEN v_YEAR := 2005; END IF;
IF FY = 13 THEN v_YEAR := 2006; END IF;
-- NB I have also tried FLOOR((to_date('01/08/' || '2004', 'DD/MM/YYYY') - to_date(DOB, 'DD/MM/YYYY'))/365.25) with the same result
v_AGE_AT_START := FLOOR(MONTHS_BETWEEN(to_date('01/08/' || v_YEAR, 'DD/MM/YYYY'),to_date(DOB, 'DD/MM/YYYY'))/12);
WHEN NO_DATA_FOUND THEN
Can anyone please advise.