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

Popular posts from this blog

ADF : Get Current Logged User Name

OAF : Get Current Row in Table

ADF : Working with ViewCriteria