Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2003
    Location
    hyderabad
    Posts
    6

    Post Unanswered: Calculation of Age

    Can any one send the function to get the age in Yr. Mon. Days in sql for oracle 7.3

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Calculation of Age

    Originally posted by gln_gln_48
    Can any one send the function to get the age in Yr. Mon. Days in sql for oracle 7.3
    I don't know what format you want the result in - e.g. 3 numbers, a formatted VARCHAR2. Anyway, you can amend the SQL below to get the result in the format you want:
    Code:
    SQL> create table t ( dt DATE );
    
    Table created.
    
    SQL> insert into t values ('23-feb-1990')
      2  /
    
    1 row created.
    
    SQL> insert into t values ('24-feb-1990')
      2  /
    
    1 row created.
    
    SQL> insert into t values ('25-feb-1990')
      2  /
    
    1 row created.
    
    SQL> select trunc(months_between(trunc(sysdate),trunc(dt))/12) years,
      2  mod(trunc(months_between(trunc(sysdate),trunc(dt))),12) months,
      3  trunc(sysdate)-add_months(trunc(dt),trunc(months_between(trunc(sysdate),trunc(dt)))) days
      4* from t
    
         YEARS     MONTHS       DAYS
    ---------- ---------- ----------
            13          0          1
            13          0          0
            12         11         30

  3. #3
    Join Date
    Feb 2003
    Location
    hyderabad
    Posts
    6

    Re: Calculation of Age

    IAM VERY THANKFUL TO THE FORUM..

    GLN SARMA






    QUOTE]Originally posted by andrewst
    I don't know what format you want the result in - e.g. 3 numbers, a formatted VARCHAR2. Anyway, you can amend the SQL below to get the result in the format you want:
    Code:
    SQL> create table t ( dt DATE );
    
    Table created.
    
    SQL> insert into t values ('23-feb-1990')
      2  /
    
    1 row created.
    
    SQL> insert into t values ('24-feb-1990')
      2  /
    
    1 row created.
    
    SQL> insert into t values ('25-feb-1990')
      2  /
    
    1 row created.
    
    SQL> select trunc(months_between(trunc(sysdate),trunc(dt))/12) years,
      2  mod(trunc(months_between(trunc(sysdate),trunc(dt))),12) months,
      3  trunc(sysdate)-add_months(trunc(dt),trunc(months_between(trunc(sysdate),trunc(dt)))) days
      4* from t
    
         YEARS     MONTHS       DAYS
    ---------- ---------- ----------
            13          0          1
            13          0          0
            12         11         30
    [/QUOTE]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •