All Forums Database
cmyeyez 11 posts Joined 02/13
24 Mar 2015
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')

MaximeV 19 posts Joined 11/13
24 Mar 2015

Hi,
search for interval date type, you can use the year(4) to month interval.
sel CURRENT_DATE - birth_date  YEAR(4)  TO MONTH
...
 

cmyeyez 11 posts Joined 02/13
24 Mar 2015

that did the trick!
Thanks!

cmyeyez 11 posts Joined 02/13
24 Mar 2015

OOPS.. I needed year,month & day
 

dnoeth 4628 posts Joined 11/04
24 Mar 2015

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

 

Dieter

cmyeyez 11 posts Joined 02/13
26 Mar 2015

thanks Dieter!
will give it a try

cmyeyez 11 posts Joined 02/13
26 Mar 2015

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

fv185003 1 post Joined 05/11
11 Feb 2016
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;

 
 
 
 
 

You must sign in to leave a comment.