A CA Business Intelligence (CABI) query based on date/time returns incorrect results if the time you are searching for is after 1:00 p.m.

Document ID : KB000050336
Last Modified Date : 14/02/2018
Show Technical Document Details

Description:

If the time you are searching for is prior to 1:00 pm then the correct results are returned.

If the time you are searching for is after 1:00 pm, then 12 hours is added to the time.

For Example:

There are 3 requests opened - one at 2:00 p.m., one at 2:00 a.m. and another at 3:00 a.m.

  • A query that searches for requests with an open date after 12:00 p.m. correctly returns all three requests.
  • A query that searches for requests with an open date after 1:00 p.m. incorrectly, only returns the requests opened at 2:00 a.m. and 3:00 a.m.
  • A query that searches for requests with an open date after 3:00 p.m. incorrectly, only returns the request opened at 3:00 a.m.
  • A query that searches for requests with an open date after 12:00 a.m. incorrectly, only returns the requests opened at 2:00 a.m. and 3:00 a.m.

Solution:

  1. Edit the file odbc.prm in the \CA\SC\CommonReporting\BusinessObjects Enterprise 11.5\win32_x86\dataAccess\connectionServer\odbc folder on the Business Intelligence server.

  2. Locate the following line.
    <Parameter Name="USER_INPUT_DATE_FORMAT">{\d 'yyyy-mm-dd HH:mm:ss am/pm'}</Parameter>
  3. Change date format for USER_INPUT_DATE_FORMAT in odbc.prm from "yyyy-mm-dd HH:mm:ss" to "yyyy-mm-dd hh:mm:ss".

  4. Save the file and restart the "Central Management Server" via the Central Configuration Manager

    Once completed, try running the above queries and check whether the correct results are returned.