Date Calculation in Query

[joanp]

Hi,

I'm trying to return the number of days between SYSDATE and EFFDT in an expression in PS Query.

I've tried:
sysdate - a.effdt
to_char(sysdate,'yyyy-mm-dd') - a.effdt
and
to_number(to_char(sysdate,'yyyy-mm-dd')) - to_number(a.effdt)

All return error msg:
SQL error. Stmt #: 4433 Error Position: 0 Return: 1722 - ORA-01722: invalid number

Does anyone know how I can perform this calculation in Query?

Thanks very much, in advance!

Regards,
Joan

 

[joanp]

Never mind... I found it!

%DateDiff(A.EFFDT,SYSDATE)