Home / Tech Tips / Tech Tip: DATETIME field format – Dealing with Milliseconds
InfoSol Blog Tech Tip

Tech Tip: 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.

Want more tips? Join the Let’s Speak BO Webinar series and gain access to a library with over 100 Webinars:

About Amy

Amy is a Senior Technical Consultant at InfoSol. In one form or another, she has been working with data for 30 years. Amy’s education and early work experience in Analytical Chemistry led to a passion in improving reporting out of Microsoft Excel-based and Access-based tools. Frustrated with the monotony of “cranking out samples,” she studied and trained to get her MCSE with Microsoft in order to follow her IT-focused passions. Amy has been with InfoSol for 19 years now. Certified as a BusinessObjects Certified Professional, she can often be found providing client installations and migrations, as well as data integration, universe, report and dashboard design. She still says her greatest passion is for process improvement in whatever form it may take.

Check Also

InfoSol Blog Tech Tip

Tech Tip: Dashboard Design like a Pro using Design Software

I have been designing dashboards with Xcelsius and Custom Code (HTML5) the past couple of ...

Leave a Reply

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