Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Ravi.

Asked: July 15, 2016 - 6:31 am UTC

Last updated: July 18, 2016 - 12:28 pm UTC

Version: 11g

Viewed 1000+ times

You Asked

How Can i create a oracle sql Function which can take two input Dates and Display their Average.For Ex:- input :-28-Aug-2016,4-Sep-2016 output:- 1-Sep-2016

and Connor said...

Something like this perhaps

SQL> with t as
  2    ( select
  3         date '2015-09-01' d1,
  4         date '2015-12-07' d2
  5      from dual )
  6  select
  7    least(d1,d2) + abs(d1-d2)/2
  8  from t;

LEAST(D1,
---------
19-OCT-15

1 row selected.


You'd need to decide what action you want to take if two dates differ by only 1 day.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Are LEAST() and ABS() really necessary?

Stew Ashton, July 18, 2016 - 11:22 am UTC

No matter what date you start with, you either subtract a positive number from the later date or subtract a negative number from the earlier date.
alter session set nls_date_format='YYYY-MM-DD HH24:MI';

with t as (
  select date '2016-07-01' d1, date '2016-07-18' d2
  from dual
)
select d1 - (d1 - d2) / 2 result1,
       d2 - (d2 - d1) / 2 result2
from t;

RESULT1          RESULT2
---------------- ----------------
2016-07-09 12:00 2016-07-09 12:00

Chris Saxon
July 18, 2016 - 12:28 pm UTC

Good point, thanks Stew.

Chris

Why not use AVG()

David Grimberg, July 25, 2016 - 4:07 pm UTC

with dts(dt) as (
select date '2015-09-01' from dual union all
select date '2015-12-07' from dual
)
select to_date(1,'j') + avg(dt-to_date(1,'j'))
from dts;

This will work for any number of dates, not just two.