MSSQL bigint Unix Timestamp for Datetime with milliseconds

Question

I have timestamps that are in bigint. Here is one:

1462924862735870900

It is up to microsecond accuracy.

I am currently using this:

SELECT DATEADD(S, CONVERT(int,LEFT(1462924862735870900, 10)), '1970-01-01') 

This gave me from time to time until the second, but I would like to maintain at least millisecond accuracy.

I understand that DATEADD cannot handle bigint, so I truncated bigint and converted it to int. If I do not, I get this error:

 Arithmetic overflow error converting expression to data type int 

I hope someone can help me find the best way to convert this and maintain at least millisecond accuracy.

Any help would be greatly appreciated. Thank!

---- UPDATE ------

With @ako, I put together a function that takes a bigint timestamp in milliseconds, microseconds, or nanoseconds and returns DATETIME2 (7), which is 100 nanosecond precision . This would probably be more efficient, but here's the function:

 CREATE FUNCTION [dbo].[fn_tsConvert] (@ts bigint) RETURNS DATETIME2(7) AS BEGIN DECLARE @ts2 DATETIME2(7) -- MILLISECOND IF(LEN(@ts) = 13) SET @ts2 = DATEADD(HH,-4,DATEADD(MILLISECOND, @ts % 1000, DATEADD(SECOND, @ts / 1000, CAST('1970-01-01' as datetime2(7))))) -- MICROSECOND IF(LEN(@ts) = 16) SET @ts2 = DATEADD(HH,-4,DATEADD(MICROSECOND, @ts % 1000000, DATEADD(SECOND, @ts / 1000000, CAST('1970-01-01' as datetime2(7))))) -- NANOSECOND IF(LEN(@ts) = 19) SET @ts2 = DATEADD(HH,-4,DATEADD(NANOSECOND, @ts % 1000000000, DATEADD(SECOND, @ts / 1000000000, CAST('1970-01-01' as datetime2(7))))) RETURN @ts2 END 

Answer 1

I think you are dealing with nanosecond accuracy. What you can get in native sql is 100 ns.

 declare @ts as bigint = 1462924862735870900 select dateadd(NANOSECOND, @ts % 1000000000, dateadd(SECOND, @ts / 1000000000, cast('1970-01-01' as datetime2(7)))) 

Result - 2016-05-11 00: 01: 02.7358709