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

Frank Kulash

Hi, Gordan,

useruseruser wrote:

Hi all,

i have this view

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

So, you;re counting 30 days as a month. That means that if today is October 28, 2015, you would want output like

ID DT YRS MONTHS DYS

---------- ----------- ---------- ---------- ----------

9 29-OCT-2014 0 12 4

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

No, there's no built-in function that does what you requested. The kind of code that you posted is what what you'll need.

You might want to use sub-queries to save intermediate results, so you can use the results of one calculation in performing another.

gorddanmilojevic-JavaNet

Hi Kulash, I need some kind of inversions of extract(month from DATE_BEGIN_WORK) or years or days, respectively

which transform 26 years, 26 months, 91 days into global accepted format

28 years, 5 months and 1 day, or any solution, just I need finaly

reports that converts 91 days to 3 months and 1 day, 26 months to 2 year and 2 months and 3 months from days

so year as above, is there any simple way how to solve this transformations?

regards

Just simple transform

26 years, 26 months, 91 days ====> 28 years, 5 months and 1 day

unknown-951199

useruseruser wrote:

is there any simple way how to solve this transformations?

Have somebody else write the code for you

Frank Kulash

Hi,

useruseruser wrote:

Hi Kulash, I need some kind of inversions of extract(month from DATE_BEGIN_WORK) or years or days, respectively

which transform 26 years, 26 months, 91 days into global accepted format

28 years, 5 months and 1 day, or any solution, just I need finaly

reports that converts 91 days to 3 months and 1 day, 26 months to 2 year and 2 months and 3 months from days

so year as above, is there any simple way how to solve this transformations?

regards

Just simple transform

26 years, 26 months, 91 days ====> 28 years, 5 months and 1 day

Sorry, there's nothing much simpler than what you're already doing. Sub-queries might make it a little simpler, but there's nothing built into Oracle SQL where you just pass the arguments and it gives the output you requested.

You could write a user-defined function, especially if you need the same kind off transformation in multiple queries. That will be slower than doing it in pure SQL, but the difference may not be significant.

gorddanmilojevic-JavaNet

Have somebody else write the code for you

- I can write this substract on my own way, I m asking if exist any invers function which will re-calculate sum of years, months and days with spliting, as exist function extract

because I need exact and exist solution as complete functions, ok if not exist I will make it myself

John_K

See the first reply to your post.

John Spencer

Something like:

SQL> with t(id, dt) as (
  2    select 1, to_date('02/06/2010','MM/DD/YYYY') from dual union all
  3    select 2, to_date('11/29/2001','MM/DD/YYYY') from dual union all
  4    select 3, to_date('02/06/2011','MM/DD/YYYY') from dual union all
  5    select 4, to_date('10/10/2011','MM/DD/YYYY') from dual)
  6  select sum(yrs) + floor((sum(months) + floor(sum(dys)/30))/12) years,
  7        mod(sum(months) + floor(sum(dys)/30),12) months,
  8        mod(sum(dys), 30) days
  9  from (select id, dt,
10              floor(months_between(sysdate,dt)/12) as yrs,
11              floor(mod(months_between(sysdate,dt),12)) as months,
12              floor(sysdate-(add_months(dt,floor(months_between(sysdate,dt))))) as dys
      from t)
13  14
SQL> /


    YEARS    MONTHS      DAYS
---------- ---------- ----------
        28          5          1

John

Scott Swank

You're just doing that a bit backward. Instead of:

1. Convert dates to years, months & days

2. Then subtract these fields

You want to:

1. Subtract dates

2. Convert the resulting number of days into years/months/days

Cheers.

BrunoVroman

Answer

Hello Gordan,

I understand that you want the SUM of all the rows.

Here are two ideas:

-1- using a date
a) SUM( TRUNC( sysdate ) - dt )
would give you the total number of days.
b) adding this number of days to, for example, DATE '1900-01-01' would give another date...
c) just subtract 1900 of the years and you have number of years,
subtract 1 of the month (1 to 12 for Jan to December) and you have the number of months
subtract 1 of the day-of-month (1 to 31) and you have the number of days.
This is of course "approximative" as months do not have the same number of days...

-2- using only the number of days
Maybe you want something else, for example: the total number of days divided by 365.25 to have the number of years, the reminder divided by 30 to have the number of months, the reminder being the number of days.

a) SUM( TRUNC( sysdate ) - dt )

would give you the total number of days.

b) divide by 365.25 (the .25 is to take leap years into account, but up to you to choose for example "365")

c) reminder number of days (total - years * 365.25): divided by 30 gives the number of months

d) reminder number of days ( total - years * 365.25 - months * 30): gives the number of days.

Here are the two options with your test data (by coincidence this give the same result in this case)

option 1:
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
                  )
, target_date AS
( SELECT DATE '1900-01-01' + SUM( TRUNC( sysdate ) - t.dt ) trgt FROM t )
, in_pieces AS
( SELECT TO_NUMBER( TO_CHAR( td.trgt, 'YYYY' ) ) - 1900 y
      , TO_NUMBER( TO_CHAR( td.trgt, 'MM' ) ) - 1 m
      , TO_NUMBER( TO_CHAR( td.trgt, 'DD' ) ) - 1 d
    FROM target_date td
)
SELECT 'result: ' ||
      TRIM( CASE WHEN ip.y = 0 THEN NULL
                  WHEN ip.y = 1 THEN '1 year '
                  ELSE TO_CHAR( ip.y ) || ' years '
            END ||
            CASE WHEN ip.m = 0 THEN NULL
                  WHEN ip.m = 1 THEN '1 month '
                  ELSE TO_CHAR( ip.m ) || ' months '
            END ||
            CASE WHEN ip.d = 0 THEN NULL
                  WHEN ip.d = 1 THEN '1 day'
                  ELSE TO_CHAR( ip.d ) || ' days'
            END
          )
FROM in_pieces ip
;
result: 28 years 4 months 28 days

option 2:

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
                  )
, nb_days AS
( SELECT SUM( TRUNC( sysdate ) - t.dt ) n FROM t )
, y AS
( SELECT nb.n, FLOOR( nb.n / 365.25 ) y FROM nb_days nb )
, ym AS
( SELECT y.n, y.y, FLOOR( ( y.n - y.y * 365.25 ) / 30 ) m FROM y )
, ymd AS
( SELECT ym.y, ym.m, ym.n - ym.y * 365.25 - ym.m * 30 d FROM ym )
SELECT 'result: ' ||
      TRIM( CASE WHEN ymd.y = 0 THEN NULL
                  WHEN ymd.y = 1 THEN '1 year '
                  ELSE TO_CHAR( ymd.y ) || ' years '
            END ||
            CASE WHEN ymd.m = 0 THEN NULL
                  WHEN ymd.m = 1 THEN '1 month '
                  ELSE TO_CHAR( ymd.m ) || ' months '
            END ||
            CASE WHEN ymd.d = 0 THEN NULL
                  WHEN ymd.d = 1 THEN '1 day'
                  ELSE TO_CHAR( ymd.d ) || ' days'
            END
          )
FROM ymd
;
result: 28 years 4 months 28 days

Best regards,

Bruno Vroman.

Marked as Answer by gorddanmilojevic-JavaNet · Sep 27 2020

1 person found this helpful

gorddanmilojevic-JavaNet

working perfect thanx

Gordan

1 person found this helpful

1 - 10

Locked Post

New comments cannot be posted to this locked post.

Locked on Nov 26 2015

Added on Oct 28 2015

10 comments

2,725 views

SQL & PL/SQL

split days, month and year in one date

Comments

Post Details