How can calculate age

**saad1** · Jan 8, 2009 08:04

hi
I need help to sql

How can calculate (age) from brith.

I have table

sql)

create table stu(id number(2),name varchar2(8),brith date,age number(2));

=======================================
id ==== name ==== brith ==== age
=======================================
01 === smith === 06/07/1980 === ????????
02 === beater === 22/10/1975 === ?????????

thanks

**guido2004** · Jan 8, 2009 08:18

See this post
(ain't that forum search function great

)

**saad1** · Jan 8, 2009 11:49

I tried this link and it dos not work;

**Dr John** · Jan 8, 2009 12:54

yes it does...

**saad1** · Jan 8, 2009 14:06

I see.
sorry
I mean the code dos not work not link

I want column "age" calculate automatic.

I enter only date in column "birth" and
column "age" calculate automatic age.

**guido2004** · Jan 8, 2009 14:15

Exactly, that's what rudy's code does. Of course, you'll have to change some table and field names

Code:

select 
    id
  , name
  , birth
  , year(current_date) 
       - year(birth)
       - case when month(current_date)
                 > month(birth)      
              then 0
              when month(current_date)
                 < month(birth)      
              then 1
              when dayofmonth(current_date)
                 < dayofmonth(birth) 
              then 1
              else 0 end    as age
  from stu

Btw, once you save the date of birth, it doesn't make much sense storing the age in your table as well, since you'd have to recalculate it everytime again anyway. Just calculate it when you select the data from the table.