Skip to Main Content

Breadcrumb

Dev Live Dev Intro

This month 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. 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;

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.

and you rated our response

  (2 ratings)

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

Reviews

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

Followup  

July 18, 2016 - 12:28 pm UTC

Good point, thanks Stew.

Chris

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.