Skip to Main Content

Evaluate function to convert presentation variable to date not working..

724120Jan 5 2010 — edited Jan 6 2010

I have a calendar prompt on my dashboard and the presentation variable for this prompt is PV1.

I am using this variable in my report to create a calculated column. The condition for the calc column runs something like

case WHEN "Time"."Day" >= ANY(SELECT saw_3 FROM(SELECT "Time"."Start Date" saw_3 FROM "xyz_subject_area" WHERE "Time".Day = CAST(EVALUATE('TO_DATE(%1,%2)' AS DATE, '@{PV1}{@{system.currentTime}[MM/DD/YYYY]}', 'MM/DD/YYYY HH:MI:SS AM' ) AS DATE)) EX1) then "ABC"."Units" else "XYZ"."Units" end

However when I checked the expression EVALUATE('TO_DATE(%1,%2)' AS DATE, '@{PV1}{@{system.currentTime}[MM/DD/YYYY]}', 'MM/DD/YYYY HH:MI:SS AM' ) is not returning the date always. I am not sure why for some rows it is working correctly and for some it is not returning any value.

Please let me know if there another better way to convert the presentation variable value to date.