What is the SQL Query to view dates from Service Desk Manager tables in Standard Date & Time format?

book

Article ID: 27535

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

Question: 

What is the SQL Query to view dates from Service Desk tables in Standard Date & Time format?

Answer: 

Service Desk Manager (SDM) stores time in UNIX format where the number of seconds is calculated from GMT 1/1/1970 12am.

We can use the SQL utility dateadd to view the dates in Standard format. Using the Call Request table as an example against e.g. the Open Date, the query executed would be as follows:

For SDM 11.x

select open_date, dateadd (ss, open_date -18000, '1/1/1970') from mdb.Call_Req

For SDM 12.x & 14.1

select open_date, dateadd (ss, open_date -18000, '1/1/1970') from dbo.Call_Req

 

Environment

Release: SDMU0M99000-14.1-Service Desk Manager-Full License
Component: