Using a Date Prompt with a Pass-Through SQL Report

[falcoholic]

Background

Simplified example - I am trying to select all rows from a table where the date is > some date the user specifies in a date prompt.
Example:
select *
from myTable
where active_date > Date specified by prompt

In report studio I created a new List report and entered the query as a Pass-Through SQL query executed against an Oracle DB Data source.
(I understand Pass-Through SQL is not always recommended. Please assume that the decision here is to go with a Pass-Through SQL query)

Problem
I included the below clause in my query to prompt for the date -

myTable.active_date >= to_date(#sq(prompt('FROM', 'date'))#,'dd-mon-yy')

However, when I execute the report and select a date from the prompt, I get the following message -
An error occurred while performing operation 'sqlOpenResult' status='-28'.

I assume this error is thrown because the data type is in mismatch. I tried converting the prompt to a string and then to a date (e.g. to_date(to_char(prompt)) ) but that did not work either. I know Cognos returns the date in YYYY-MM-DD (2012-05-01) but my Oracle database stores dates as DD-MON-YY (01-MAY-12) - is this discrepancy the issue?


Any advice on this issue? I am very new to Cognos and the above is the extent of my knowledge, so an explanation in basic terms would be much appreciated.

Side Note
As a relevant side note,  I did get the report to run successfully if I used a free-form text input prompt instead of a date prompt, but this requires that the user manually type in a date like '16-MAR-12' instead of conveniently selecting it from a calendar. I used the following clause in my Pass-Through SQL query -
myTable.active_date >= to_date(#prompt('FROM')#)

[bi4u2]

Why don't you just add a filter to the query in Report Studio like this Active_Date >= ?From_Date? (assuming that ?From_Date? is the name of the parameter for the date the user selects. The filter you describe looks like a filter you would put on a query subject in Framework Manager and not Report Studio. Where are you applying this filter?

[falcoholic]

Thanks bi4u2.

I have the prompt(...) command worked in directly into my SQL WHERE clause

SELECT *
FROM myTable
WHERE myTable.active_date >= to_date(#sq(prompt('FROM', 'date'))#,'dd-mon-yy')


Before moving down the path of applying the filter through report studio, I wanted to see if there was anything fundamentally (or syntactically) incorrect with my above statement. Is there a reason that particular error is being thrown?

Thanks again!

[pricter]

If you use a calendar prompt the string that returns is of the following format
2012-05-15
so in the mask you should put something like 'YYYY-MM-DD'

Unfortunately I do not have an oracle database to play with it but I assume that is the problem