Calculating age from birth date in sql

Question

I am executing sql queries using oracle. I need to calculate age from birth date given.

select 
    sname,
    TIMESTAMPDIFF(YEAR, DATEOFBIRTH, CURDATE()) 
from 
    sailors 
where 
    (select 
         MAX(TIMESTAMPDIFF(YEAR, DATEOFBIRTH, CURDATE())) 
     FROM sailors) TIMESTAMPDIFF(YEAR, DATEOFBIRTH, CURDATE())

where dateofbirth is my column name and sailors is table name. However, it is showing error, missing keyword.

Answer 1

Ther where clause is used to provide the condition and you have a select statement there. That's the reason you get the error.

If you need just the diff wrt date of birth, then use

SELECT sname,(DATEOFBIRTH - sysdate) AS "Days"
FROM sailors

That should suffice

Answer 2

Gives the row with max age in Oracle:

select sname, age from (
  select sname, extract(year from current_date) - extract (year from dateofbirth) age,
         row_number() over(order by extract(year from current_date) - extract (year from dateofbirth) desc) rw
  from sailors
) where rw = 1;

extract(year from current_date) - extract (year from dateofbirth) difference in years between to dates

row_number() over(order by age desc) - numerates the rows according to ORDER BY

Answer 3

Usually age is given in whole years. So, months_between() gives the number of whole months between two dates; dividing by 12 gives number of years; and applying a floor() removes the unwanted fraction.

select sname
        , floor(months_between(sysdate, dateofbirth)/12) as age_in_yrs
from sailors
/

If you want to get the oldest sailor(s) then you can deploy this as a sub-query:

with cte as ( select sname
                  , floor(months_between(sysdate, dateofbirth)/12) as age_in_yrs
              from sailors )
select *
from cte
where age_in_yrs = ( select max(age_in_yrs) from cte )
/

Oracle has a wealth of datetime functions, which for historical reasons often have names different from their equivalents in other flavours of RDBMS. Find out more.

---

'It is showing "not a valid month".'

This can only happen when casting a string to a date. So presumably your DATEOFBIRTH column doesn't have a DATE datatype. And the strings in it don't match the default NLS_DATE_FORMAT. A string like this 12.23.1993 would throw ORA-01843 if the default date format is dd.mm.yyyy

So you need to write an explicit cast, thus:

select sname
        , floor(months_between(sysdate, to_date(dateofbirth, 'dd.mm.yyyy'))/12) as age_in_yrs
from sailors
/

Note that I have had to guess whether your dates are DD.MM or MM.DD because it's not obvious from the sample you give; adjust the mask as you need.

Answer 4

select sname, extract(year from sysdate) - extract (year from dateofbirth ) age
from sailors 

--or 

select to_number(to_char(sysdate,'yyyy') - to_number(to_char( dateofbirth ,'yyyy')) 
from sailors  

Answer 5

Here is a T-SQL that can calculate age of a person. Accurate to the day of birth.

select case 
            when cast(getdate() as date) = cast(dateadd(year, (datediff(year, '1996-09-09', getdate())), '1996-09-09') as date)
                then dateDiff(yyyy,'1996-09-09',dateadd(year, 0, getdate()))
            else dateDiff(yyyy,'1996-09-09',dateadd(year, -1, getdate()))
        end as MemberAge
go

Answer 6

A little more about sql age calculation, as APC pointed it out, integer age can be given by the following expression:

floor(months_between(sysdate, dateofbirth)/12)

But, months_between function treats end of month day specially. And this may raise an issue for leapers (someone born on the 29th of Feb), as taken from Wikipedia: The effective legal date of a leapling's birthday in non-leap years varies between jurisdictions.

Putting in an example, on 28/02/1998, was someone born on 29/02/1988 10 years old or 9? Previous expression would yield 10, but in some areas it's 9.

In Teradata, this expression seems to be commonly used to allow leapers only turn one year old on 1st of March, instead of on 28 Feb, in non-leaping years:

select (extract(year from current_date) - extract(year from BIRTH_DATE) (named YEARS))
+ case when current_date - (YEARS (interval year)) < BIRTH_DATE
then -1 else 0 end

I think this expression should have the same effect and no case when has to been invoked:

floor(least(months_between(CURRENT_DATE, BIRTH_DATE), months_between(CURRENT_DATE - 1, BIRTH_DATE - 1))/12)

I ran the following code to check the validity of this expression in Teradata:

select O.DATEO, N.DATEN,
(EXTRACT(YEAR FROM N.DATEN) - EXTRACT(YEAR FROM O.DATEO) (NAMED YEARS))
      + CASE WHEN ADD_MONTHS( N.DATEN , - YEARS * 12 )  < O.DATEO
THEN -1 ELSE 0 END  AS AGE1,
floor(least(months_between(N.DATEN, O.DATEO), months_between(N.DATEN -1, O.DATEO- 1))/12) as AGE2,
AGE1 - AGE2
from
(select cast(calendar_date as date) as DATEO from sys_calendar.CALENDAR
where calendar_date between date '2000-01-01'  and date '2013-01-01') as O
inner join 
(select cast(calendar_date as date) as DATEN from sys_calendar.CALENDAR
where calendar_date between date '2000-01-01'  and date '2013-01-01'
) as N
on N.DATEN > O.DATEO
where AGE1 <> AGE2;