COGNOiSe.com - The IBM Cognos Community

IBM Cognos 8 Platform => COGNOS 8 => Report Studio => Topic started by: m23 on 03 Nov 2008 07:01:08 PM

Title: Using a date prompt in user defined SQL.
Post by: m23 on 03 Nov 2008 07:01:08 PM
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.

Title: Re: Using a date prompt in user defined SQL.
Post by: m23 on 04 Nov 2008 12:52:29 AM
It's ok I worked it out. Cognos passes the prompt in the format 'YYYY-MM-DD'.
Title: Re: Using a date prompt in user defined SQL.
Post by: Suraj on 04 Nov 2008 11:21:09 AM
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')