AGE calculation

Posted on 2005-04-28
Last Modified: 2011-11-30
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

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

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;

Can anyone please advise.

Question by:BEDCOLMIS
    LVL 14

    Accepted Solution

    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;
    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;
    LVL 11

    Expert Comment

    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.


    Author Comment

    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

    We are on Oracle 8

    NB Ignore the QSTART variable, I intend to use that later.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Suggested Solutions

    Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
    From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
    This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
    This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now