Error when using date as condition 1

[funkmonsteruk]

I'm trying to create a report utilising a date field as a condition. However I seem to keep on getting this error message during execution:

Error during SQL execution (DA0003)
(IBM)(CLI Driver)(DB2) SQL0180N The Syntax of the string representationof a datetime value is incorrect. SQLSTATE=22007|SQLExecute)=180

I've used other date fields from the same database but haven't had any similar problems.... Any suggestions?

 

[webi2002]

First check on the Universe to see if it is a date column, or date with time stamp.
Second, if it is a time stamp, then you should check on the query so that it reflects the time stamp. A way around it, is that if it is a time stamp, you can create an object in the universe that only picks the first ten characters, and then revert it to a date format, then you can use prompts against is since it is in a short date format.
Last if it is a short date to begin with, then check with your DBA as far as the sequel at hand.

 

[blom0344]

Since Funkmonsteruk is working with DB2 I do not think that the procedure of picking the first 10 positions and the subsequent transformation to date is needed if he uses the DATE() function directly. This should automatically transform a timestamp to a datefield.
The list of values of such an object will be very much smaller too, since the time-part is lost in the process... T. Blom
Information analyst
tbl@shimano-eu.com

 

[funkmonsteruk]

How would i use the DATE() function directly?

 

[blom0344]

In designer go to properties of the object , go to the select part , push >> button. You will see a selection of database specific functions among them DATE(). Wrap this around you timestamp object.......... T. Blom
Information analyst
tbl@shimano-eu.com

 

[funkmonsteruk]

Thats fantastic Blom0344, cheers for your help

 

[BrianTyler]

Casting the Timestamp to a Date will work, but the DB2 will not use an index on the Timestamp (if any). A preferred solution would be for Business Objects to properly understand Timestamps.