Date conversion from bigint to datetimestamp

Question

Table (EVENTS) definition:

ID bigint, EVENTTIMESTAMP bigint
3361592,    132446022372460000

How can T convert this to date time stamp? I know from another UI that the date time stored is 15-September-2020 10:10:37 AM.

Can I get how to convert this using SQL queries for SQL Server?

What I tried:

SELECT *, CONCAT
          (
               CAST(DATEADD(SECOND, EVENTTIMESTAMP/100000000 ,'1970/1/1') AS DATE)
              ,' '
              ,CAST(DATEADD(SECOND, EVENTTIMESTAMP/100000000 ,'1970/1/1') AS TIME)
          ) EVENTTIME
FROM EVENTS
where ID = '3361592';

Msg 8115, Level 16, State 2, Line 14 Arithmetic overflow error converting expression to data type int. — shyam, Sep 16 '20 at 03:05
https://stackoverflow.com/questions/15030474/sql-using-dateadd-with-bigints — lastr2d2, Sep 16 '20 at 03:33

score 0 · Answer 1 · answered Sep 16 '20 at 03:40

0

You can try the below -

select *,
dateadd(s, convert(bigint, EVENTTIMESTAMP) / 100000000, convert(datetime, '1-1-1970 00:00:00'))
FROM EVENTS
where ID = '3361592'

answered Sep 16 '20 at 03:40

Fahmi

37,315
5
22
31

Returns the following 2011-12-21 09:37:03.000 what was expected is 15-September-2020 10:10:37 AM. Is there some issue with calculation or UTC conversion? – shyam Sep 17 '20 at 02:20

Date conversion from bigint to datetimestamp

1 Answers1