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.

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_AGE1 21/04/1999 17 182 23/04/1999 17 183 29/04/1999 17 184 24/04/1999 17 185 15/04/1999 17 186 14/04/1999 17 187 20/04/1999 17 188 22/04/1999 17 189 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