1 Reply Latest reply on Apr 4, 2017 9:53 AM by FTisiot

    Presentation Variable conversion to Date Format

    user13816768

      Hi,

       

      We have two fields on a dashboard prompt, Close Month and Release Month End Date.

       

      The Close Month is actually a date field, but user would like to see it in 'MMM-YYYY' format, so we have changed the date field to varchar.

      eg: EVALUATE('TO_CHAR(%1,%2)' AS CHARACTER (30),"Close Month",'MON-YYYY')

       

      The release month end date is a date field (mm/DD/yyyy)and we need to print the > value which is in 'Closed Month' prompt.

      eg: if user selected Jan 2017 in Close Month, we have to show values > Jan 2017 in Release Month End prompt i.e., from Feb 2017 forward

       

      So we have passed presentation variable to the 'Close Month' prompt 'PV1' and trying to fetch the data through sql

      eg: SELECT     Release Month End date  FROM "xyz" WHERE Release Month End Date  > '@{pv1}'  ORDER BY 1

       

      Because of varchar in the Close Month and comparing against the Date field in the Release Month End Date, the formula is not working.. we have used multiple cast and evaluate functions to change presentation variable which is in varchar to date.. but not working..

       

      Could you please provide a better solution for this?

       

      Thanks!