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

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: