Calculate age from birth date

Database
Fan

Calculate age from birth date

I have a field that contains birth date. How do I calculate age in years using a function or interval conversion?
7 REPLIES
Enthusiast

Re: Calculate age from birth date

There must be an easier way than this :o

SELECT EXTRACT (YEAR FROM CURRENT_DATE) - EXTRACT (YEAR FROM DOB ) +
CASE
WHEN EXTRACT(MONTH FROM CURRENT_DATE) < EXTRACT (MONTH FROM DOB) THEN -1
WHEN EXTRACT(MONTH FROM CURRENT_DATE) = EXTRACT (MONTH FROM DOB) THEN
CASE WHEN EXTRACT (DAY FROM CURRENT_DATE) < EXTRACT (DAY FROM DOB) THEN -1 ELSE 0 END
ELSE 0
END AS AGE
;

Senior Apprentice

Re: Calculate age from birth date

Try
(current_date - birthdate) year as age

Dieter
Fan

Re: Calculate age from birth date

I tried that and got this error: 7453: Interval field overflow.

I discovered that the default precision of the year interval is 2, so I change it to 4 and it works great. The improved calculation is:

(current_date - birthdate) year(4) as age

In most cases year(3) will work, but our database has a few erroneous birth dates.

Thanks for your help.
Enthusiast

Re: Calculate age from birth date

Try this

SEL
(CURRENT_DATE - CAST(DOB AS DATE))/365 (TITLE 'Years')
,((CURRENT_DATE - CAST(DOB AS DATE)) MOD 365 ) /30 (TITLE 'Months')
FROM EDW_DBA_DB.TEST;

One assumption here I have made is all months have 30 days and Years have 365 days.

Vinay
Enthusiast

Re: Calculate age from birth date

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
New Member

Re: Calculate age from birth date

Run 13/04/2017:

 

SyntaxEditor Code Snippet

SyntaxEditor Code Snippet
SELECT BIRTH_DATE, (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  AS CORRECT_AGE,
(CURRENT_DATE - BIRTH_DATE) YEAR AS INCORRECT_AGE produces the following:

Birth_Date CORRECT_AGE INCORRECT_AGE
1 21/04/1999 17 18
2 23/04/1999 17 18
3 29/04/1999 17 18
4 24/04/1999 17 18
5 15/04/1999 17 18
6 14/04/1999 17 18
7 20/04/1999 17 18
8 22/04/1999 17 18
9 19/04/1999 17 18

(current_date - birthdate) year(4) also incorrectly will calculate as 18

 

Highlighted
Senior Apprentice

Re: Calculate age from birth date

The code doing Interval calculation was from 2007, before the logic was modified to match Standard SQL, see https://community.teradata.com/t5/Database/Age-Calculation/m-p/72117/highlight/true#M30072 for a simple calculation