This discussion is archived
8 Replies Latest reply: Apr 20, 2013 4:16 AM by Frank Kulash RSS

function problem

user12957777 Newbie
Currently Being Moderated
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.

Please suggest the solution.

Sanjay
Edited by: user12957777 on Dec 28, 2012 11:48 PM
  • 1. Re: function problem
    yoonas Expert
    Currently Being Moderated
    Hi,

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

    Regards
    Yoonas
  • 2. Re: function problem
    Etbin Guru
    Currently Being Moderated
    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
    user12957777 Newbie
    Currently Being Moderated
    I Apex, its still showing 1936.

    Sanjay
  • 4. Re: function problem
    981337 Newbie
    Currently Being Moderated
    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
    yoonas Expert
    Currently Being Moderated
    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
    Frank Kulash Guru
    Currently Being Moderated
    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
    user12957777 Newbie
    Currently Being Moderated
    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
    Frank Kulash Guru
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points