One year = 365 days, one month = 31 days

Select 1 as year, 5 as month, 10 as day from dual
union all
Select 1 as year, 10 as month, 25 as day from dual

Need resault as:

years  month  day
3      4      4

CodePudding user response：

We can use some algorithm to calculate the number of days, based on conditions

• one year = 365 days
• one month = 31 days

1. Year : (year * 365 month * 31 day) and division by 365 to get the total year of number.
2. Month : (month * 31 day) and division by 31 to get the total month of number, but we need to get mod from that because total month numbers will be carried if that higher than 12.

as this query.

select CAST(SUM((year * 365   month * 31   day) / 365) AS INT) years  ,
       CAST(MOD(SUM(month * 31   day)/31, 12) AS INT) month,
       MOD(SUM(day) , 31) day
from 
(
  Select 1 as year, 5 as month, 10 as day from dual
  union all
  Select 1 as year, 10 as month, 25 as day from dual 
) t1;

sqlfiddle

CodePudding user response：

If 1 year = 365 days and 1 month = 31 days then:

SELECT TRUNC(SUM(year*365   month*31   day)/365) AS years,
       TRUNC(MOD(SUM(month*31   day), 365)/31) AS month,
       MOD(MOD(SUM(month*31   day), 365), 31) AS day
FROM
(
  Select 1 as year, 5 as month, 10 as day from dual
  union all
  Select 1 as year, 13 as month, 25 as day from dual 
) t1;

Which outputs:

YEARS	MONTH	DAY
3	7	11

If 1 year = 12 months and 1 month = 31 days then you want:

SELECT TRUNC(SUM(year   month/12   day/31/12)) AS years,
       TRUNC(MOD(SUM(month   day/31), 12)) month,
       MOD(SUM(day) , 31) day
FROM
(
  Select 1 as year, 5 as month, 10 as day from dual
  union all
  Select 1 as year, 13 as month, 25 as day from dual 
) t1;

Which outputs:

YEARS	MONTH	DAY
3	7	4

db<>fiddle here