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')
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
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
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
ORDER BY eff_dt;