Tip: Error found at 00
Trick: Truncating datetime to seconds
We recently worked with a customer to provide a solution to a datetime field in their PI SQL database (OSI) that was returning a SQL parsing error when using it in a query filter. Specifically, the error stated “Error found at 00”.
Looking into the datetime field we can see that the field contains milliseconds or “microseconds”. Because milliseconds are not supported by BusinessObjects, the solution first involves truncating the datetime to seconds. SAP KB 2692589 refers to this in regards to PostGreSQL.
is useful in developing OSI data. For PostGreSQL, we can use date_trunc(‘second’,my_table.my_timestamp_column) in the syntax for the object.
Following this change to the actual SQL, we can further specify the datetime format under the advanced tab of the object to ‘yyyy-mm-dd HH:m:ss’ and/or make this change across all connections of this type by adding it to the PRM file for this database type, changing the USER_INPUT_DATETIME_FORMAT to ‘yyyy-mm-dd HH:m:ss’ or whatever is appropriate for your data.