Skip to Main Content
  • Questions
  • Calculating age in terms of years,months and days

Breadcrumb

May 4th

Question and Answer

Tom Kyte

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

You Asked

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

Comments

SQL commands

Dave Swift, April 16, 2009 - 7:32 am UTC

Thanks Tom, it's always good to be remember what you can do in SQL directly.

Amazaing

anchal, June 19, 2012 - 12:38 pm UTC

Thanks tom, perfect answer to my question!!