Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

split days, month and year in one date

gorddanmilojevic-JavaNetOct 28 2015 — edited Oct 29 2015

Hi all,

i have this view

with t(id, dt) as (select 1, to_date('02/06/2010','MM/DD/YYYY') from dual union all
                   select 2, to_date('11/29/2001','MM/DD/YYYY') from dual union all
                  select 3, to_date('02/06/2011','MM/DD/YYYY') from dual union all
                  select 4, to_date('10/10/2011','MM/DD/YYYY') from dual
                  )
  --
-- end of test data
  --
   select id, dt
        ,floor(months_between(sysdate,dt)/12) as yrs
        ,floor(mod(months_between(sysdate,dt),12)) as months
         -- The day will be ambiguous because of the different number of days in a month
         -- you could devise your own algorithm to give your desired result, but this is a good approximation
        ,floor(sysdate-(add_months(dt,floor(months_between(sysdate,dt))))) as dys
  from   t
  /
        ID DT                 YRS     MONTHS        DYS
---------- ----------- ---------- ---------- ----------
         1 06-FEB-2010          5          8         22
         2 29-NOV-2001         13         10         29
         3 06-FEB-2011          4          8         22
         4 10-OCT-2011          4          0         18

result is 26 years, 26 months, 91 days.


And need substract YRS, MONTHS and DYS as format

clear years, months from 1 to 12 and days from 1 to 30 as

91 days = 30 x 3 + 1  => 1 day plus 3 months

26 months + 3 months = 29 months => 24 months + 5 months = 2 years + 5 months

and 26 years + 2 years=28 years

desire result  : 28 years, 5 months and 1 day.

Is there any function which give me this result from above query?

regards,

Gordan

This post has been answered by BrunoVroman on Oct 28 2015
Jump to Answer

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Nov 26 2015
Added on Oct 28 2015
10 comments
2,633 views