AGE calculation

Hi
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
returns 54

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

--DECLARE
v_AGE_AT_START number(20,2);
v_YEAR number(20,2);

BEGIN
--INITIALISE
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);

return v_AGE_AT_START;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN 0;

Can anyone please advise.

END fn_AGE_AT_START;
/
BEDCOLMISAsked:
Who is Participating?
 
Muhammad Ahmad ImranConnect With a Mentor Database DeveloperCommented:
How about this:

Simple One.....

create or replace function dob(db date) return varchar2 is
a varchar2(200);
mns number;
yr number;
mn number;
ds number;
begin
select trunc(trunc(months_between(sysdate,db))/12) into yr from dual;
select trunc(mod(months_between(sysdate,db),12)) into mn from dual;
select trunc((round(mod(months_between(sysdate,db),12),5)
- trunc(mod(months_between(sysdate,db),12)))*30) into ds from dual;
a := to_char(yr)||' years '||to_char(mn)||' months '||to_char(ds)||' days';
return a;
end;
/
0
 
sujit_kumarCommented:
I have executed your function in EMP table for hire date. I got the desired result. See below,

select hiredate, fn_AGE_AT_START(to_char(hiredate, 'DD/MM/YYYY'), to_char(sysdate, 'DD/MM/YYYY'), 12) diff from emp

Row#      HIREDATE      DIFF

1      12/17/1980      24
2      2/20/1981      24
3      2/22/1981      24
4      4/2/1981      24
5      9/28/1981      23
6      5/1/1981      24
7      6/9/1981      24
8      4/19/1987      18
9      11/17/1981      23
10      9/8/1981      23
11      5/23/1987      18
12      12/3/1981      23
13      12/3/1981      23
14      1/23/1982      23

Post somee test cases (some data) to generate the error that you are facing.

Sujit
0
 
BEDCOLMISAuthor Commented:
Here are some examples that work as a singular select statement:

select fn_AGE_AT_START('28/03/1950', '01/01/2000', '11') from dual  -- Returns 54
select fn_AGE_AT_START('28/03/1986', '01/01/2000', '11') from dual -- Returns 18
select fn_AGE_AT_START('01/08/1976', '01/01/2000', '11') from dual - Returns 28

However if you use the function in a standard select statement for the above 3 rows I get
1954
1918
1928

We are on Oracle 8

NB Ignore the QSTART variable, I intend to use that later.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.