Database
Enthusiast

## Getting a person's exact age

Hi,

how do i write an SQL in teradata to compare the DOB and current date to get the exact age? I can't do a (current_date - DOB) / 365 as it is not that accurate due to leap yrs...
5 REPLIES 5
Highlighted
Enthusiast

## Re: Getting a person's exact age

you may try this:

select ((current_date - date '1990-10-31') year(4)) as age
Enthusiast

## Re: Getting a person's exact age

Thanks. I just realise I need to add the year(4)...
Enthusiast

## Re: Getting a person's exact age

select ( cast('2012-01-01' as date) - cast('1989-01-04' as date)) year(4) as abc;

This similar query returns age 23  three days before the person turns 23, so the YEAR(4) format isn't adjusting for leap years either.

Enthusiast

## Re: Getting a person's exact age

This does work to the exact date for specific dates:

SELECT (EXTRACT(YEAR FROM DATE '2012-01-04') - EXTRACT(YEAR FROM DATE '1989-01-04') (NAMED YEARS))

+ CASE WHEN DATE '2012-01-04' - (YEARS (INTERVAL YEAR)) < DATE '1989-01-04'

THEN -1 ELSE 0 END AS age_whole_yrs;

Or for use with the current date and a birth date in your database:

SELECT (EXTRACT(YEAR FROM CURRENT_DATE) - EXTRACT(YEAR FROM brth_dt) (NAMED YEARS))

+ CASE WHEN CURRENT_DATE - (YEARS (INTERVAL YEAR)) < brth_dt

THEN -1 ELSE 0 END AS age_whole_yrs;

Enthusiast

## Re: Getting a person's exact age

Hi JJx5,

One problem I found with this age calculation - The calculation errors out when the date you are calculating from is 2/29/yyyy. Because you are subtracting years from this date it can result in an invalid date error (eg - 2/29/2011).

The best age calculation I have found is:

(EXTRACT(YEAR FROM ((CAST(CURRENT_DATE AS DATE)-CAST(birth_date AS DATE)) YEAR(4) TO MONTH))) -

CASE WHEN EXTRACT(MONTH FROM CURRENT_DATE) = EXTRACT(MONTH FROM BIRTH_DATE) AND EXTRACT(DAY FROM BIRTH_DATE) > EXTRACT(DAY FROM CURRENT_DATE) THEN 1 ELSE 0 END AS age

I have yet to find any case where this does not work, but I am sure there is at least one case out there somewhere.

-Kevin