Home / Highlights / Tech Tip: DATETIME field format – Dealing with Milliseconds

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 10 years now. Certified as a BusinessObjects Certified Professional, she can often be found providing client installations and migrations, as well as universe, report and dashboard design. In addition to BusinessObjects products, she is well-versed in Version Manager and 360View. Recently, Amy has completed migrations to XI3.x for Sohar Aluminium in Oman and XI 4.0 for WAI Global in Florida, but she admits she still has the greatest passion for process improvement in whatever form it may take.

Check Also

Tech Tip: Content Validation for Reports Scheduled via Email

Business(s) and their units send hundreds to thousands of emails out via report scheduling. Delivering ...

Leave a Reply

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