convert varchar UTC to human datetime

Chris Jones
Chris Jones used Ask the Experts™
on
Hello, i have a column in my database that is a varchar, and I am trying to convert it to a date time the time is in UTC, and I need to get this into Human read date time.

time stamp
1488219517998


SQL that returns the wrong dates
Select 
CAST(SWITCHOFFSET(CAST(DATEADD(SECOND,CONVERT(bigint, CreateTime) /1000 + CONVERT(bigint, CreateTime) % 1000 + 8*60*60, '19700101') AS DATETIMEOFFSET),'-05:00') AS DATETIME2)
from ME_API_Requests_History
WHERE Technician = 'chris jones'
AND Status = 'Open'

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
Maybe this?:

Select CAST(DATEADD(HOUR, -5, DATEADD(SECOND, CAST(CreateTime as bigint)/1000, '19700101')) AS datetime2) AS datetime
From (
    Select CAST(1488219517998 AS bigint) AS CreateTime
) AS sample_data

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial