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

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

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