SQL & PL/SQL

Hi,

For a report, I need to convert days as months and days for eg 370 days as 12 months 5 days.

I tried below query
with t as (
select
maturity_date - value_date tenor
from account_master)
select
trunc(mod(months_between(trunc(sysdate,'YYYY')+ tenor,trunc(sysdate,'YYYY')),30)) month,
(trunc(sysdate,'YYYY')+tenor)-add_months(trunc(sysdate,'YYYY'),trunc(months_between(trunc(sysdate,'YYYY')+ tenor,trunc(sysdate,'YYYY')))) "day"
from t

And i am getting output as

MONTH day
---------- ----------
11 30
23 30
11 30
1 5
11 30
11 30
11 30
0 6
11 30
for Number of days
TENOR
----------
365
730
365
36
365
365
365
6
365

As you can see for 365 days i am getting Months as 11 and days as 30
and for 730 days i am getting months as 23 and days as 30.

I want 365 days as 12 months and 0 days.

I think it is months_between function that is creating this problem.

Can anyone suggest another way of accomplishing this?

Thanks and Regards
Amit Trivedi