Hi all,
i have a table like this
create table test
(
appln varchar2(50), -- application name
applndt date, -- stores application created date
upddt date --stores application last updated date)
/


I have inserted records as follows

insert into test values('app1',to_date('01-jan-1990 10:30:00','dd-mon-yyyy hh:mi:ss'),to_date('19-sep-2002 03:00:00','dd-mon-yyyy hh:mi:ss'));

insert into test values('app2',to_date('01-jan-1995 10:30:00','dd-mon-yyyy hh:mi:ss'),to_date('19-sep-2002 03:00:00','dd-mon-yyyy hh:mi:ss'));

Now I want a query which displays the output as follows

app1 12 years 8 Months 18 days 4 hrs 30 min
app2 7 years 8 months 18 days 4 hrs 30 min

how do i write a query to get the above output

regards
Ravi

to give you a hint: have a look at:

select appln,
upddt-applndt as TotalDaysgone,
MONTHS_BETWEEN (upddt,applndt) as Totalmonthsgone,
trunc(MONTHS_BETWEEN (upddt,applndt)/12) as Yearsgone,
MONTHS_BETWEEN (upddt,applndt) - trunc(MONTHS_BETWEEN (upddt,applndt)/12)*12 as RestOfMonths,
trunc(MONTHS_BETWEEN (upddt,applndt) - trunc(MONTHS_BETWEEN (upddt,applndt)/12)*12) as Monthsgone
from test;

you could/would need the same nesting for days and hours etc.
personally I would rather write a stored procedure (or package) to achieve the same job.
rgds