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.

book

Article ID: 50336

calendar_today

Updated On:

Products

CA IT Asset Manager CA Software Asset Manager (CA SAM) ASSET PORTFOLIO MGMT- SERVER SUPPORT AUTOMATION- SERVER CA Service Desk Manager - Unified Self Service KNOWLEDGE TOOLS CA Service Management - Asset Portfolio Management CA Service Management - Service Desk Manager

Issue/Introduction

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.

Environment

Release:
Component: ARGIS