All Forums Database
jefflimpc 6 posts Joined 05/07
18 Nov 2007
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...

nithyanandam 65 posts Joined 10/04
19 Nov 2007

you may try this:select ((current_date - date '1990-10-31') year(4)) as age

jefflimpc 6 posts Joined 05/07
03 Dec 2007

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

jjx5nc 7 posts Joined 01/12
04 Jan 2012

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.

JJx5

jjx5nc 7 posts Joined 01/12
04 Jan 2012

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;

JJx5

kbtrumptmp 21 posts Joined 01/09
19 Jul 2013

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

 

You must sign in to leave a comment.