Hi All,

Date prompts in a pas through SQL is not working.
Cognos version is 10.2 and database is Db2.

FIlter in the pass through SQL
 
date(OrderDate ) between date(TIMESTAMPFORMAT (?Start_Date? ,'Mon DD, YYYY')) and date(TIMESTAMPFORMAT(?End_Date?,'Mon DD, YYYY'))

When hardcoded values of Oct 20, 2024 is substituted in this filter it works.

Throwing a datasource adaptor error when prompts are used
XQE-DAT-0001 Data source adapter error: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601

Also tried using these formats, nothing works.
AND date(OrderDate )=date(TO_DATE(?P_StartDate? ,'YYYY-MM-DD'))


Using order date between ?P_StartDate? and ?P_EndDate? as detail filter in the query is working. But this is not an option Since it is a union query and results of first part filtered by date needs to be applied to second query.

Quote from: actcognosuser on Yesterday at 11:50:49 AMHi All,

Date prompts in a pas through SQL is not working.
Cognos version is 10.2 and database is Db2.

FIlter in the pass through SQL
 
date(OrderDate ) between date(TIMESTAMPFORMAT (?Start_Date? ,'Mon DD, YYYY')) and date(TIMESTAMPFORMAT(?End_Date?,'Mon DD, YYYY'))

When hardcoded values of Oct 20, 2024 is substituted in this filter it works.

Throwing a datasource adaptor error when prompts are used
XQE-DAT-0001 Data source adapter error: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601

Also tried using these formats, nothing works.
AND date(OrderDate )=date(TO_DATE(?P_StartDate? ,'YYYY-MM-DD'))


Using order date between ?P_StartDate? and ?P_EndDate? as detail filter in the query is working. But this is not an option Since it is a union query and results of first part filtered by date needs to be applied to second query.

Hi,

Have you tried using prompt macros in your SQL rather than Cognos parameters? For example, replacing ?Start_Date? with #prompt('Start_Date','Date')#

Is there a reason you are coding this as SQL rather than adding detail filters to each query that feeds into your Union object? If you must use SQL, why are you using pass-thru SQL rather than Cognos SQL (or Native SQL)?

Cheers!

MF.