What is the SQL Query to view dates from MDB tables in standard date & time format?

Products

• CA Service Desk Manager
• CA Service Management - Service Desk Manager

Issue/Introduction

CA Service Desk Manager stores dates and times as the number of seconds since the Unix epoch (January 1, 1970, at 00:00:00 GMT). 

What is the SQL Query to view dates from CA Service Desk tables in standard date & time format?

Environment

CA Service Desk Manager - all versions

Resolution

Use the SQL utility 'dateadd' to view the dates in standard format.

Using the Call Request table (Call_Req) as an example, 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 and 17.x

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