04 August, 2011

Average on Dates Columns

SA,
As we know that is is impossible to calculate average on non numeric column.

SQL> select avg(hire_date) from scott.emp;

It will raise error
ORA-00932: inconsistent datatypes: expected NUMBER got DATE 

There is workaround to calculate avg on date columns.
Workaround is to convert date to julian date and then calculate average.


So the following is solution to make average on date column
SELECT TO_DATE(TRUNC(AVG( TO_CHAR(hire_date,'J'))),'J') FROM EMP;

Thanks

1 comment:

  1. Very helpful post. I have worked on this issue for many days but didn't find a solution. I really appreciate your knowledge as you have suggested such a nice solution to calculate average on non numeric columns like dates. Thanks for sharing.
    sap pp

    ReplyDelete

ADF : Scope Variables

Oracle ADF uses many variables and each variable has a scope. There are five scopes in ADF (Application, Request, Session, View and PageFl...