Impromptu Filter Date Problem 1

[jazzercise]

I am new to Cognos Impromptu and I am trying to create a filter for a report that allows the user to select the date range of the report. I am using UDB database on Impromptu version 6.0.

My filter prompt looks like this:

date(field1) >= ?\Begin Date? and date(field1) <= ?\End Date?

field1 = a date timestamp field
?\Begin Date? = date prompt field
?\End Date? = date prompt field

Error message:

Error number -239:

DMS-E-GENERAL, A general exception has occurred during operation 'prepare request'.
DMS-E-GENERAL, A general exception has occurred during operation 'prepare request'.
[IBM][CLI Driver][DB2/SUN] SQL0007N The character &quot;{&quot; following &quot;ATE(T1.&quot;EVNT_TS&quot >=&quot; is not valid. SQLSTATE=42601

I have tried several other scenarios - without any luck.

Any ideas would be appreciated!

Thanks!

 

[JGirl]

Try this, (no guarantees though!), its straight from the cognos support site:

***********************************************
Description:

After upgrading DB2 UDB to version 7.2 the following error occurs when applying a filter (end_dt = 2000-12-31) to a DATE size 4 item...[IBM][CLI Driver][DB2/6000]SQL0007N The character &quot;{&quot; following
&quot and (T3.END_DT&quot; =&quot; is not valid. SQLSTATE=42601

DB2 UDB 7.2 is a rebundled version of DB2 UDB 7.1 fix pack 3.


Solution:

Add the line
NOSCAN=0
under the [COMMON] or [Database name] section of the db2cli.ini file.
***********************************************

Hope this helps....

Cheers
J

 

[Recce]

Hi

First try and convert the Date/Time stamp to Date field and then build your prompt on the Date field. That is if you do not need to filter on time. :->

 

[sln007]

Use the following:
If Field1 is defined as a TIMESTAMP type then.....

&quot;(datetime-to-date(Field1) between (?\Begin-date? and ?\End-date?))&quot;

The variable &quot;Field1&quot; needs to be of the same type (date) as the prompts.

We've used this method many times.