• Questions
• # Average of input two Dates

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

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 we 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

### 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```

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.