Passing the value of a date prompt into tabular SQL

[Janine]

Hi,

I need the value of a date prompt in a tabular SQL, but ?Start Date? and #prompt('Start Date','DATE')# don't work for me. I got the error:

QE-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions' status='-9'. UDA-SQL-0446 Oracle returned an error message. ORA-00932: inconsistent datatypes: expected DATE got NUMBER UDA-SQL-0450 An unexpected error encountered during the "prepare" operation.

Any suggestion around it?

Thanks a lot

Janine

[jethro89]

Hi, I'm not sure how you're using your prompt, but you need to make sure that on your filter, you're using a to_date (or whatever database function your database uses), and the native date format in Cognos is yyyy-mm-dd.
Hope that helps.

[CVR]

Hi
I too have similar problem. I used DatePrompt, but has given the error " QE-DEF-0381 Invalid format for prompt 'start_date'. Expected format for date: CCYY-MM-DD.
QE-DEF-0381 Invalid format for prompt 'end_date'. Expected format for date: CCYY-MM-DD. " in validation. But when run the report is giving results correctly. Could any one knows whey this validation is failing here???

Thanks in advance for any help.

CVR

[dfite]

I use an Oracle database, and to reformat date prompt values, I always have to use:

TO_DATE(?pRecipDate?, 'YYYY-MM-DD')

Of course, this doens't seem to work in all circumstances for me, as I can use this to put the date value in the body on my report, but it errors out when I put it into a page header (which makes absolutely no sense to me).

[Suraj]

I use the following and works fine.
to_date(#prompt('Start_Date')#,'yyyy-mm-dd')

[iamcognosuser]

Hi,

Thank you very much suraj. Your suggestion really helped me a lot.