CODEWITHSUNDEEP
sqloracle-database
user_odoo
user_odoo

Reputation: 2358

Sum year, month and day - Oracle SQL

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

Upvotes: 2

Views: 321

Answers (2)

MT0
MT0

Reputation: 167774

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

Upvotes: 1

D-Shih
D-Shih

Reputation: 46219

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

Upvotes: 1

Related Questions