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

    function problem

    user12957777
      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
          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
            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
              I Apex, its still showing 1936.

              Sanjay
              • 4. Re: function problem
                981337
                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
                  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
                    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
                      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
                        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.