DATETIME field format – Dealing with Milliseconds

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.

The function

TruncateToWholeSeconds()

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.

About Amy

Check Also

Let's Speak BO Webinar - Upcoming Event

Webinar: Top 10 Features in Webi v4.3 on Tuesday, November 1st

This live demo will introduce you to my top 10 features of Webi v4.3 SP …

Leave a Reply

Your email address will not be published. Required fields are marked *