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

How to Pass Parameters to ActionListener in ADF

In some cases, it is required to pass a value to ActionListener of ADF Button. The method that can be invoked by actionListeners has only...