Using a date prompt in user defined SQL.

[m23]

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.

[m23]

It's ok I worked it out. Cognos passes the prompt in the format 'YYYY-MM-DD'.

[Suraj]

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')