SQL Macro for integer column

[Mfg]

SQL:

Select * from A
Where Date between #Prompt('From','Integer')# and #Prompt('To','Integer')#

Prompt Page:
Date Prompt : From
Date Prompt : To

How am i suppose to pass dates from 'From' and 'To' date prompts to the prompt macros?

i tried following:

Select * from A
cast(cast(Date as varchar(10)) as date) between cast(cast(#Prompt('From','Integer')# as varchar(10)) as date) and cast(cast(#Prompt('To','Integer')# as varchar(10)) as date)

SQL Validations were fine but running report gives me this error:
'The query contains one or more unresolved prompts.QE-DEF-0387 Invalid format for numeric prompt 'From'.QE-DEF-0387 Invalid format for numeric prompt 'To'

Then I tried:

Select * from A
cast(cast(Date as varchar(10)) as date) between #Prompt('From','date')# and #Prompt('To','date')#

SQL Validation Error:
UDA-SQL-0107 A general exception has occurred during the operation "prepare".
[IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0401 - Comparison operator BETWEEN operands not compatible.

My client is with Cognos 10.2.1.
does anybody come across this problem?
Any help guys!

Thanks in advance.

[MFGF]

SQL:

Select * from A
Where Date between #Prompt('From','Integer')# and #Prompt('To','Integer')#

Prompt Page:
Date Prompt : From
Date Prompt : To

How am i suppose to pass dates from 'From' and 'To' date prompts to the prompt macros?

i tried following:

Select * from A
cast(cast(Date as varchar(10)) as date) between cast(cast(#Prompt('From','Integer')# as varchar(10)) as date) and cast(cast(#Prompt('To','Integer')# as varchar(10)) as date)

SQL Validations were fine but running report gives me this error:
'The query contains one or more unresolved prompts.QE-DEF-0387 Invalid format for numeric prompt 'From'.QE-DEF-0387 Invalid format for numeric prompt 'To'

Then I tried:

Select * from A
cast(cast(Date as varchar(10)) as date) between #Prompt('From','date')# and #Prompt('To','date')#

SQL Validation Error:
UDA-SQL-0107 A general exception has occurred during the operation "prepare".
[IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0401 - Comparison operator BETWEEN operands not compatible.

My client is with Cognos 10.2.1.
does anybody come across this problem?
Any help guys!

Thanks in advance.

Hi,

Why are you coding SQL in your report? It's a really (really really really) bad idea to build reports in this way. Can you explain?

Why have you set the parameter type to Integer in your prompt macros? What is the underlying datatype of the items you are filtering on? Dates? Can you explain?

MF.

[Mfg]

Thanks for the reply MF.
I got lot of complex joins in the SQL provided, i am trying to use SQL itself instead of doing joins in the FM between table.
So actually the Date column is in decimal format but that one exactly giving outputs like this 20161001 format.

I tried in reverse order also:

select * from A
where date between cast(#Prompt('From','date')# as Integer/decimal) and cast(#Prompt('To','date')# as Integer/decimal)

The SQL getting validated, no execution errors. But output is
'No Data Available'

[MFGF]

Thanks for the reply MF.
I got lot of complex joins in the SQL provided, i am trying to use SQL itself instead of doing joins in the FM between table.
So actually the Date column is in decimal format but that one exactly giving outputs like this 20161001 format.

I tried in reverse order also:

select * from A
where date between cast(#Prompt('From','date')# as Integer/decimal) and cast(#Prompt('To','date')# as Integer/decimal)

The SQL getting validated, no execution errors. But output is
'No Data Available'

Honestly, I'd have to advise that coding SQL directly in your reports like this is a dangerous approach. If I was you I'd look at how you can create a model in FM that delivers appropriate results, and spend some time getting this right.

What result do you get if you take one of you cast() statements and include it as a column? Are you seeing the dates presented in yyyymmdd format? If you were not using manually coded SQL you could use the Cognos Extract() function to extract the year, month and day parts and mush them together. Using manual SQL rules this out, though.

MF.