Need Help to fix Date parameter while integrating BI report into Dashboard
I have created a SQL with a date parameter that calculates effective date minus 5 days.
Below is the code which is working well while running normally with BI reports.
AND (: P_EFFECTIVE_DATE-5) BETWEEN A.EFFECTIVE_START_DATE AND A.EFFECTIVE_END_DATE
The same code failed with the error ": ORA-01722: invalid number" while using the report in the dashboard.
Even I am using this code and getting the same error "ORA-01722: invalid number":
AND (:P_EFFECTIVE_DATE-5) BETWEEN to_char(trunc(A.EFFECTIVE_START_DATE), 'yyyy-MM-dd') AND to_char(trunc(A.EFFECTIVE_END_DATE), 'yyyy-MM-dd')
Please suggest a way to fix it. So, that I could use this bi report in Dashboard.