Optional Date Prompt to Native SQL

[ora1001]

I have a optional date prompt(parameter name: dateparam) in the user interface.
required=no, calendar type is gregorian, range = no.

i have set the default text param to a space so that where clause is not generated if there is no value entered.

select ....
from table
where ....
and #prompt('dateparam',
             'date',
                     ' ',
             ' and table.date = ' )#

For testing purposes, I have selected Jan 25, 2017 in the prompt. The back end value in the database is 25-JAN-17.
I am getting this error when i try to run the report. Is the syntax correct ?
UDA-SQL-0107 A general exception has occurred during the operation "prepare".ORA-00932: inconsistent datatypes: expected DATE got NUMBER

Thanks in advance...

[CognosPaul]

Date prompts usually return values in YYYY-MM-DD. Try wrapping the value in to_date:


select ....
from table
where ....
#prompt('dateparam',
             'date',
                     ' ',
             ' and table.date = to_date(','',sq('YYYY-MM-DD')+')' )#

[ora1001]

Appreciate it Paul.
The report validation went fine but when i run the report it throws the below error.
UDA-SQL-0107 A general exception has occurred during the operation "prepare".ORA-00907: missing right parenthesis

Also Paul, what would be the best way to learn these syntax rules especially those quotes ... is there any tool or page where we can see how it gets translated to sql so that we can see the conversion..
for example the syntax below
#prompt('dateparam', -- name of parameter
             'date', -- data type
                     ' ', -- default text (when empty means its optional)
             ' and table.date = to_date(','',sq('YYYY-MM-DD')+')' )#
              -- text: which gets added to the sql. how does this part is converted to sql.

             

[CognosPaul]

Whoops, try this:
#prompt('dateparam',
    'date'
  , ' '
  , ' and table.date = to_date('''
  ,''
  ,''','+sq('YYYY-MM-DD')+')' )
#

The goal is to make the output of the macro look like:  and table.date = to_date('2017-01-26','YYYY-MM-DD')

You can use double single quotes to create a quote. So the three quotes in the pre-text parameter create "and table.date = to_date('"

An easy way of testing your macro is to wrap the entire thing in the sq function and put it in a new query. Just view tabular to check the output.