COGNOiSe.com - The IBM Cognos Community

I'm trying to return the maximum date in a price table based on two user prompts, one of which work fine, but when I substitute out the hardcoded Date "where" statement and put in the prompt, it stops working.

Anybody got any ideas ??

Works

SELECT
   MAX(effective_dt) as Max_Date
FROM
   holding.fx_rate
WHERE
   effective_dt <= '2005-03-23' and
   to_ccy = 'USD' and
   from_ccy = #prompt('from_ccy')#


Doesn't work


SELECT
   MAX(effective_dt) as Max_Date
FROM
   holding.fx_rate
WHERE
   effective_dt <= date(#prompt('trade_dt','date')#) and
   to_ccy= 'USD' and
   from_ccy= #prompt('from_ccy')#

Thanks
Dave

You are doing all the right things ...

seems to me that there is a mismatch between the date value entered by the user and the date datatype used by the ReportNet server.

Perhaps the server is using a date style that is different.

To see if this is the case try creating two calculated columns one for the date prompt entered by the user and the other a cast of a string literal to a date value and see if they both match.

If they do not then you know what the problem.

Let me know, I am interested.

Thanks
NKT

Dave...helloooooo...wake up

Finally found my own solution.

Turns out when you are using a macro prompt to distinguish a date you should encapsulate the whole thing in the sq command (Single quotes) this way the db will interpret it correctly.

e.g

Won't work
date = #prompt('date_input','date')#

Will Work
date = #sq(prompt('date_input','date'))#

thanks for your help anyway

Dave