passing presentation variable - date field error

Question

Hi All,

could anyone please provide some suggestion - I have a field in the RPD which is based on the to_date and it is to get the last day of the month

1)OPV Field - LAST_DAY(TO_DATE(TO_CHAR(TO_DATE(A.REC_CALNDR_PRD,'YYYYMMDD'),'MM/DD/YYYY'),'MM/DD/YYYY')) AS MONTH_ENDING

2). From the Answers it looks like below without any format in the column properties data format

3). For testing I create the prompt in answers with a presentation variable - and I choose choice list - so the values in it looks like

4). when I specify the filter using presentation variable - Month Ending is equal to / is in  @{vtest} - This is working when I open the answers but when I look at the results it is giving an error "date value was expected"

when we look at the results in answers it should not give me an error - right? it should specify the message or custom message like no results etc

I think I am missing something here - thanks a lot for yur time

Rj

Thomas Dodds · Answer

In addition to your other issues ... you are burdening the OPV (which is not a performance approach in itself) ...

1)OPV Field - LAST_DAY(TO_DATE(TO_CHAR(TO_DATE(A.REC_CALNDR_PRD,'YYYYMMDD'),'MM/DD/YYYY'),'MM/DD/YYYY')) AS MONTH_ENDING

You don't need half the functions...

LAST_DAY(TO_DATE(A.REC_CALNDR_PRD,'YYYYMMDD')) AS MONTH_ENDING

Khalid Ibrahim · Answer

You can also default in the current date or create a repository variable that you can then default to in your prompt.

Bose Pandian · Answer

Hi,

In Answer, if you want to test the result, then just update the default column with any date value and try. It will not give any error.