ORA-08161 Error using a date prompt

[cognos2014]

My report uses the date prompt for the below query. However I get an ORA-01861: literal does not match format string error message. My 'time__out' field is a combination of date and timestamp hence the trunc.

trunc(time_out) between to_date(#prompt('Parm_Start_Date')#,'YYYY-MM-DD' ) and to_date(#prompt('Parm_End_Date')#,'YYYY-MM-DD' )

Help please since I have tried all possible options trying to figure this out!

Thanks

[MFGF]

My report uses the date prompt for the below query. However I get an ORA-01861: literal does not match format string error message. My 'time__out' field is a combination of date and timestamp hence the trunc.

trunc(time_out) between to_date(#prompt('Parm_Start_Date')#,'YYYY-MM-DD' ) and to_date(#prompt('Parm_End_Date')#,'YYYY-MM-DD' )

Help please since I have tried all possible options trying to figure this out!

Thanks

Hi,

Is there a reason for using prompt macros like this?  You could perhaps just try this as your filter expression:

[time-out] between ?Parm_Start_Date? and ?Parm_End_Date?

MF.

[cognos2014]

Thanks for the response MF.

I am using a SQL query and want to restrict my rows in the SQL instead of doing on the query, hence the prompt macro. I also tried what you suggested and still get the error.

FYI.. my time_out is a date type filed and an example of how the data looks is (02/19/2014 1:15:00 PM)

[hanfrie]

Hello sinrag2005,

What exactly are the values used for the two prompts that trigger the error?
What do you get when you change your prompt format string from 'YYYY-MM-DD' into 'YYYYMMDD', without the '-'?

Hanfrie