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.
There are two ways of returning the standard date format:
- Use a conversation utility, such as the Online Unix Time conversion utility available on the internet at:
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.'
- 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;