How to obtain in a standard date/time format the values of 'open_date' and 'close_date' fields stored in the call_req table.

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
• CA Service Desk Manager
• CA Service Management - Asset Portfolio Management
• CA Service Management - Service Desk Manager

Issue/Introduction

Description:

The format in which Service Desk stores the fields 'open_date' and 'close_date' in the call_req table is, by default, in the UNIX format where number stored is the number of seconds from GMT 1/1/1970 12:00 am. This is by design and it affects all the date/time fields of the product in the mdb. As a result, executing a simple select that returns the date/time field from the mdb will give us a large integer value and not a standard date/time.

Solution:

There are two ways of returning the standard date format:

1. Use a conversation utility, such as the Online Unix Time conversion utility available on the internet at:
   
   http://www.onlineconversion.com/unix_time.htm
   
   to convert this value into a readable date format.
   
   For example, entering '1196652086' in the Unix Time Stamp field returns 'Mon, 3 Dec 2007 03:21:26 UTC.'
   
   
2. Execute a database query to return the field in the standard date/time format:
   
   The SQL Server database syntax is:
   
   select open_date, dateadd (ss,open_date,'1/1/1970') from call_req
   
   The Oracle database syntax is:
   
   select to_char( to_date('01011970','ddmmyyyy') + 1/24/60/60 * open_date, 'mm-dd-yyyy hh24:mi:ss') as open_date from mdbadmin.call_req;

Environment

Release:
Component: ARGIS