[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
Solved

AGE calculation

Posted on 2005-04-28
Medium Priority
5,228 Views
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;

END fn_AGE_AT_START;
/
0
Question by:BEDCOLMIS

LVL 14

Accepted Solution

ID: 13884074

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

LVL 11

Expert Comment

ID: 13884085
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

Author Comment

ID: 13884138
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

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Suggested Courses
Course of the Month17 days, 22 hours left to enroll