8 Replies Latest reply: Apr 20, 2013 6:16 AM by Frank Kulash

# function problem

``````Table : - Employee
----------------------------
CREATE TABLE EMPLOYEE
(
EMP_ID   NUMBER;
BIRTH_DT DATE;
)

Function: - CALC_AGE (Calculate employee age as on sysdate)
----------------------------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION CALC_AGE(P_DATE DATE)
RETURN NUMBER IS L_AGE NUMBER;
BEGIN
SELECT EXTRACT(YEAR FROM NUMTOYMINTERVAL(MONTHS_BETWEEN(TRUNC(SYSDATE),TO_DATE(P_DATE,'DD-MM-YYYY')),'MONTH'))
INTO   L_AGE
FROM   DUAL;
RETURN NVL(L_AGE,0);
END CALC_AGE;

Query:
---------

SELECT EMP_ID, CALC_AGE(BIRTH_DT)AGE
FROM   EMPLOYEE

Query Output:
-------------------
If suppose the employee birth date is 16-06-1976 then output should come as 34, whereas the query output comes as 1936.

Sanjay``````
Edited by: user12957777 on Dec 28, 2012 11:48 PM
• ###### 1. Re: function problem
Hi,

``ALTER SESSION SET NLS_DATE_FORMAT='DD-MM-RRRR' ;``
Run the query after setting the date format

Regards
Yoonas
• ###### 2. Re: function problem
No Database at hand to try to reproduce.
Wouldn'd returning <tt><b>(trunc(sysdate) - to_date(p_date,'dd-mm-yyyy')) / 365.25</b></tt> rounded or truncated be good enough (less functions called) considering months_between is using 31 days months in internal calculations.

Regards

Etbin
• ###### 3. Re: function problem
I Apex, its still showing 1936.

Sanjay
• ###### 4. Re: function problem
Hi,

CREATE OR REPLACE procedure
CALC_AGE(P_DATE DATE)
as
l_age number;
l_age1 number;
months number;
BEGIN
select (trunc(sysdate)-trunc(p_date))/365 into l_age from dual;
l_age1:=floor(l_age);
months:=floor((l_age-l_age1)*12);
dbms_output.put_line('No of Years and Months : '||l_age1||' '||months);
END CALC_AGE;
/

begin
calc_age(to_date('15-MAY-1987','DD-MON-YYYY'));
end;
/

Thanks!
• ###### 5. Re: function problem
Try this one
``````CREATE OR REPLACE
FUNCTION CALC_AGE(
P_DATE DATE)
RETURN NUMBER
IS
L_AGE NUMBER;
BEGIN
SELECT EXTRACT(YEAR FROM NUMTOYMINTERVAL(MONTHS_BETWEEN(TRUNC(sysdate),TO_DATE(P_DATE,'DD-MM-RR')),'MONTH'))
INTO L_AGE
FROM DUAL;
RETURN NVL(L_AGE,0);
END CALC_AGE; ``````
Can you just post your application date format if above code does not work

Edited by: yoonus on Dec 29, 2012 2:24 AM
• ###### 6. Re: function problem
Hi, Sanjay,
user12957777 wrote:
``````... Function: - CALC_AGE (Calculate employee age as on sysdate)
----------------------------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION CALC_AGE(P_DATE DATE)
RETURN NUMBER IS L_AGE NUMBER;
BEGIN
SELECT EXTRACT(YEAR FROM NUMTOYMINTERVAL(MONTHS_BETWEEN(TRUNC(SYSDATE),TO_DATE(P_DATE,'DD-MM-YYYY')),'MONTH'))
INTO   L_AGE
FROM   DUAL;
RETURN NVL(L_AGE,0);
END CALC_AGE; ``````
The extra years are caused by your use of TO_DATE. As the name suggests TO_DATE takes something that is not already a DATE (a VARCHAR2, to be precise) and converts it to a DATE. The 1st argument to TO_DATE should be a VARCHAR2, but you're calling it with a DATE. If you just make p_date the 2nd argument to MONTHS_BETWEEN, then the function will return the right number.
``````CREATE OR REPLACE FUNCTION CALC_AGE(P_DATE DATE)
RETURN NUMBER IS L_AGE NUMBER;
BEGIN
SELECT  EXTRACT (    YEAR
FROM NUMTOYMINTERVAL ( MONTHS_BETWEEN ( TRUNC (SYSDATE)
, P_DATE
)
, 'MONTH'
)

)
INTO   L_AGE
FROM   DUAL;
RETURN NVL(L_AGE,0);
END CALC_AGE; ``````
There are a number of ways to make this function simpler and more efficient.

Instead of converting the results of MONTHS_BETWEEN into an INTERVAL, and then using EXTRACT, I would just divide and TRUNCate the number of months by 12. (Etbin was talking about MONTHS_BETWEEN being inaccurate; that applies only the fractional part. For calculating the age rounded down to the year, MONTHS_BETWEEN is completely accurate.)

You don't need the dual table very much in PL/SQL. Instead of
``````SELECT  x
INTO    y
FROM    dual;``````
you can simply say
``y := x;``
If a function always returns the same value when given the same arguments, then you should declare it as DETERMINISTIC. That doesn;t always makes calling the function faster, and it never makes it slower.

Try to make the function you right today solve the problems that will come up tomorrow. For example, instead of calucating the age as of 00:00:00 today, you could write a function the returns the age as of any given DATE. If you make the argument optional, you can have it default to midnight, so you don't have to pass the extra argument if you don't want to.

If I really needed a user-defined function, then I would use something like this:
``````CREATE OR REPLACE FUNCTION calc_age
(  p_date    DATE
, ref_date   DATE   DEFAULT  TRUNC (SYSDATE)
)
RETURN NUMBER
-- calc_age returns the number of full years from p_date to ref_date
DETERMINISTIC
IS
BEGIN
RETURN  TRUNC ( MONTHS_BETWEEN ( ref_date
, p_date
)
/ 12
);
END calc_age; ``````
Since user-defined functions always have a performance impact, I would generally not write a function for something like this, but just call MONTHS_BETWEEN directly.
• ###### 7. Re: function problem
``````The below statement gives output  as zero when the date is 01-04-2013

SELECT EXTRACT(YEAR FROM NUMTOYMINTERVAL(MONTHS_BETWEEN(TRUNC(sysdate),TO_DATE(P_DATE,'DD-MM-RR')),'MONTH'))
FROM DUAL;

Sanjay``````
• ###### 8. Re: function problem
Hi, Sanjay,
user12957777 wrote:
``The below statement gives output  as zero when the date is 01-04-2013``
Do you mean when SYSDATE is that, or when p_date is that?
What is the data type of p_date? Earlier, you had a function, where p_date was a DATE, and it was explained that passing a DATE as the first argument to TO_DATE is wrong.

Post a complete test script that people can run to re-create the problem.
Also, post the results you want from the script, and an explanation of why you want those results.
``````SELECT EXTRACT(YEAR FROM NUMTOYMINTERVAL(MONTHS_BETWEEN(TRUNC(sysdate),TO_DATE(P_DATE,'DD-MM-RR')),'MONTH'))
FROM DUAL;``````
Whenever you have a qustion, don't forget to ask it.
Back in December, you were interested in gettting the the age, in years. Is that still what you want? If so, what's wrong with the solutions you got back in December?

Is 0 the correct answer in this case? If so, what is your point? Do you want to show one way of getting that result? Would you like someone to explain why the output is 0?
Is 0 not the answer you want? If so, what is the right answer? How do you figure that? Post a few different values of p_date, and the correct answer you want (for the same given SYSDATE) for each one.

Whatever you want to do, I don't believe the code above is a very good way to do it. In Oracle, you rarely need so many conversions from one data type to another ina single expression.
Also, if this is part of some PL/SQL code (which I suppose it is, since there is no p_date column in dual), then there's no reason to use the dual table, or to do this as a SELECT.