I have a very simple script in TSQL that tries to convert a timestamp in milliseconds to a
DECLARE @Time AS BIGINT
SET @Time = 1413381394000
@Time - DATEDIFF(MILLISECOND, GETDATE(), GETUTCDATE()), CAST('1970-01-01 00:00:00' AS DATETIME)) AS [Datetime]
Arithmetic overflow error converting expression to data type int.
Your calculation for local offset has the potential to be wrong by an hour due to Daylight Savings Time.
DATEDIFF(MILLISECOND, GETDATE(), GETUTCDATE()) will only get the current offset and not the offset for the given date. Conversions to and from UTC and local time are generally best handled in application or SQLCLR code due to SQL's lack of functionality for this purpose. See How can I get the correct offset between UTC and local times for a date that is before or after DST?.
In DATEADD (Transact-SQL) Microsoft states that:
Is an expression that can be resolved to an int that is added to a datepart of date. User-defined variables are valid.
If you specify a value with a decimal fraction, the fraction is truncated and not rounded.
Therefore, you cannot directly work with millisecond values larger than the maximum value for an int which supports a range of -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647) as stated in int, bigint, smallint, and tinyint (Transact-SQL). You'll have to do to separate date adds and some modulo division.
DECLARE @Time bigint DECLARE @Seconds int DECLARE @RemainingMilliseconds int DECLARE @EpochDate datetime SET @Time = 1413381394000 SET @EpochDate = '1970-01-01 00:00:00' SET @Seconds = @Time / 1000 SET @RemainingMilliseconds = @Time % 1000 SELECT DATEADD(MILLISECOND, @RemainingMilliseconds, DATEADD(SECOND,@Seconds, @EpochDate))