Averaging dates in oracle sql

sql oracle date average
user1261700 picture user1261700 · Apr 25, 2012 · Viewed 21.9k times · Source

Is there a way to average multiple dates in oracle? avg doesn't do any good.

Thanks.

Answer

Dan A. picture Dan A. · Apr 25, 2012

The definition of an "average date" is subjective, but you could convert your dates to a Julian number, then average those, round it off, then convert back to a date.

create table dates (dt DATE);

insert into dates 
values ('24-APR-2012');
insert into dates 
values ('01-JAN-2012');
insert into dates 
values ('01-JAN-2013');
insert into dates
values ('25-DEC-1900');


select to_date(round(avg(to_number(to_char(dt, 'J')))),'J')
from dates;

Here's the SQL Fiddle: http://sqlfiddle.com/#!4/98ce9/1

Related questions

Updating a date in Oracle SQL table

I am trying to update a date in a SQL table. I am using Peoplesoft Oracle. When I run this query: Select ASOFDATE from PASOFDATE; I get 4/16/2012 I tried running this query UPDATE PASOFDATE SET ASOFDATE = '11/21/2012'; but it …

Read More
Check if current date is between two dates Oracle SQL

I would like to select 1 if current date falls between 2 dates through Oracle SQL. I wrote an SQL after reading through other questions. https://stackoverflow.com/questions/2369222/oracle-date-between-query https://stackoverflow.com/questions/2399753/select-from-table-by-knowing-only-date-without-time-oracle But it returned only null. sysdate is …

Read More
Oracle SQL - DATE greater than statement

As the title says, I want to find a way to check which of my data sets are past 6 months from SYSDATE via query. SELECT * FROM OrderArchive WHERE OrderDate <= '31 Dec 2014'; I've tried the following but it returns …

Read More