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

book

Article ID: 54753

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 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