Hi I have a report that I need to filter based on a date. I have setup a date prompt. This needs to be passed to the sql it can't be done in a filter. The database is Oracle.
I have tried
where no=(select no from table where dateField=to_date(#prompt('pDatePrompt')#,'DD/MM/YYYY') )
where no=(select no from table where dateField=to_date(#prompt('pDatePrompt', 'Date')#,'DD/MM/YYYY'))
where no=(select no from table where dateField= '?pDatePrompt?' )
where no=(select no from table where dateField= to_date('?pDatePrompt?','DD/MM/YYYY'))
The errors I get are
ORA-01858: a non-numeric character was found where a numeric was expected
or
ORA-01861: literal does not match format string
If I do where no=(select no from table where dateField= to_date('04/11/2008','DD/MM/YYYY'))
It works
ARGGHH this is so annoying. Does anyone know how I can pass the value from the date prompt into my SQL?. Maybe the format mask I am using is incorrect but when I put the value returned from the prompt into my report I get '04/11/2008' which seems right.
It's ok I worked it out. Cognos passes the prompt in the format 'YYYY-MM-DD'.
Yes you are correct.
This is how I use:
between to_date(#prompt('StartDate')#,'yyyy-mm-dd') and to_date(#prompt('EndDate')#,'yyyy-mm-dd')