Skip to Main Content


Dev Live Dev Intro

We are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August and September. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Connor McDonald

Thanks for the question, Ravi.

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

Answered by: Connor McDonald - Last updated: July 18, 2016 - 12:28 pm UTC

Category: SQL*Plus - 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 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;


1 row selected.

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

and you rated our response

  (2 ratings)

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


Are LEAST() and ABS() really necessary?

July 18, 2016 - 11:22 am UTC

Reviewer: Stew Ashton from Paris, France

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.


Why not use AVG()

July 25, 2016 - 4:07 pm UTC

Reviewer: David Grimberg from United States

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.