AGE in MONTHS & YEARS

Database
Enthusiast

AGE in MONTHS & YEARS

need to calculate Age (Years & Months) from a BIRTH_DATE  and account for leap years, months with 28,30 and 31days..

I'd also like to concatenate the fields but the code below doesn't seem to be working.

 sel (CURRENT_DATE - CAST(birth_date AS DATE))/365 (TITLE 'Years') || '-' || ((CURRENT_DATE - CAST(birth_date AS DATE)) MOD 365 ) /30 (TITLE 'Months')

7 REPLIES
Teradata Employee

Re: AGE in MONTHS & YEARS

Hi,

search for interval date type, you can use the year(4) to month interval.

sel CURRENT_DATE - birth_date  YEAR(4)  TO MONTH

...

Enthusiast

Re: AGE in MONTHS & YEARS

that did the trick!

Thanks!

Enthusiast

Re: AGE in MONTHS & YEARS

OOPS.. I needed year,month & day

Junior Contributor

Re: AGE in MONTHS & YEARS

Of course the combination of (year, month, day) is not a fixed number of days, so you must define some rules, e.g. is between 2012-02-29 and 2013-02-28 a full year?

MONTHS_BETWEEN uses this logic, e.g.

    cast(months_between(current_date, birthdate) as int) / 12 as yr, 
cast(months_between(current_date, birthdate) as int) mod 12 as mth,
current_date - add_months(birthdate, cast(months_between(current_date, birthdate) as int)) as dy,
add_months(birthdate, yr*12 + mth) + dy -- check, should return the same current_date for all rows
Enthusiast

Re: AGE in MONTHS & YEARS

thanks Dieter!

will give it a try

Enthusiast

Re: AGE in MONTHS & YEARS

looks like my current version (13.11.0.06) doesn't support the months_between function :/

Teradata Employee

Re: AGE in MONTHS & YEARS

TRY THIS:

SELECT
calendar_date AS eff_dt
,DATE '1980-06-07' AS birth_dt
,EXTRACT(YEAR FROM eff_dt)-EXTRACT(YEAR FROM birth_dt)
+
CASE WHEN EXTRACT(YEAR FROM eff_dt)=EXTRACT(YEAR FROM birth_dt) THEN 0
   WHEN EXTRACT(MONTH FROM eff_dt)-EXTRACT(MONTH FROM birth_dt)<0 THEN -1
    WHEN  EXTRACT(MONTH FROM eff_dt)=EXTRACT(MONTH FROM birth_dt) AND EXTRACT(DAY FROM eff_dt)-EXTRACT(DAY FROM birth_dt)<0 THEN -1
    ELSE 0
END AS age_in_years
,CAST((eff_dt-birth_dt)/365.25 AS INTEGER) AS age_in_years_approx

FROM SYS_CALENDAR.calendar AS C
WHERE C.year_of_calendar='2015'
ORDER BY eff_dt;