Problems with Prompts 1

[ba543]

I am working with Business Objects 5.1 and I am trying to prompt between two dates. I first had to convert the date so that it could be typed into a prompt as mm/dd/yyyy. That has worked fine. The problem I'm having is that every time I prompt for the date it doesn't return any specific data. I have put in a couple of dates but when I do that it seems to pull every record. I am pulling from and oracle database. When I use the exact same SQL in SQL Navigator it gives me the exact information that I need, but as soon as I transition it over to business objects it doesn't work. Here is the SQL below. The first SQL is the one from Business Objects. The Second SQL is taken from Business Objects and put into SQL Navigator the only thing I changed was the prompts, to show actual dates. Theres over 19,000 records from The Second SQL in (SQL Nav). I get No data to fetch when the SQL is in (BO). Sorry for this being so long. Any help would be greatly appreciated.


SELECT
RPT210ERROR_HAND.ERROR_SYSTEM_INDICATOR,
RPT210ERROR_HAND.ERROR_FIELD,
RPT210ERROR_HAND.ERROR_VALUE,
RPT210ERROR_HAND.CDDCD_SUB_CODE,
RPT210ERROR_HAND.CDDCD_CODE_LIST,
RPT210ERROR_HAND.ERROR_PK_IDENT
FROM
RPT210ERROR_HAND
WHERE
(
to_char(to_date(substr(RPT210ERROR_HAND.ERROR_TIMESTAMP,1,10),'yyyy/mm/dd'),'MM/DD/YYYY') >= @variable('Enter Begin Date, Example (mm/dd/yyyy)')
AND to_char(to_date(substr(RPT210ERROR_HAND.ERROR_TIMESTAMP,1,10),'yyyy/mm/dd'),'MM/DD/YYYY') <= @variable('Enter End Date, Example (mm/dd/yyyy)')
)




SELECT
RPT210ERROR_HAND.ERROR_SYSTEM_INDICATOR,
RPT210ERROR_HAND.ERROR_FIELD,
RPT210ERROR_HAND.ERROR_VALUE,
RPT210ERROR_HAND.CDDCD_SUB_CODE,
RPT210ERROR_HAND.CDDCD_CODE_LIST,
RPT210ERROR_HAND.ERROR_PK_IDENT

FROM
RPT210ERROR_HAND
WHERE
(
to_char(to_date(substr(RPT210ERROR_HAND.ERROR_TIMESTAMP,1,10),'yyyy/mm/dd'),'MM/DD/YYYY') >= '12/01/2003'
AND to_char(to_date(substr(RPT210ERROR_HAND.ERROR_TIMESTAMP,1,10),'yyyy/mm/dd'),'MM/DD/YYYY') <= '12/16/2003'
)

 

[Shoot]

Try using @prompt function in the place of @variable and also don't convert the date to the left of the operand to "to char" (I mean just do a date comparision rather than string comparision), it might help you.

Keep Smiling
Shoot