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

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

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;