• Questions
• # Calculating age in terms of years,months and days

Thanks for the question.

Asked: April 14, 2009 - 4:20 pm UTC

Last updated: April 15, 2009 - 3:12 pm UTC

Version: 9i

Viewed 10K+ times! This question is

Hi,

I am new to Oracle.Though Oracle has latest versions, in my office its still 9i.

I am using To_number(to_date(date2)-to-date(date1))/365 to get number of days in between those two date. Similarly I can do to get number of months and number of years. But I want to display all together as years:months:days in one field.

I think I need to write PL/SQL but I don't have quite right direction.Plz help!!!

## and Tom said...

you cannot use 365, years do not have a constant number of days (nor do months)

You do not want or need plsql at all.

(also, date1-date2 is already a NUMBER, the to_number is truly not necessary. timestamp1-timestamp2 is an INTERVAL which is handy for elapsed times)

```ops\$tkyte%ORA9IR2> create table t ( x date );

Table created.

ops\$tkyte%ORA9IR2> insert into t values ( to_date( '15-mar-1965', 'dd-mon-yyyy' ) );

1 row created.

ops\$tkyte%ORA9IR2>
ops\$tkyte%ORA9IR2> select ( systimestamp - cast( x as timestamp ) ) year to month from t;

(SYSTIMESTAMP-CAST(XASTIMESTAMP))YEARTOMONTH
---------------------------------------------------------------------------
+000000044-01

ops\$tkyte%ORA9IR2> select trunc(months_between(sysdate,x)/12) years,
2         months_between(sysdate,x)-12*trunc(months_between(sysdate,x)/12) months
3    from t;

YEARS     MONTHS
---------- ----------
44          1

```

two ways - not using a bit of procedural code.

## Rating

(2 ratings)

Is this answer out of date? If it is, please let us know via a Comment