mordack550 mordack550 - 1 year ago 193
SQL Question

T-SQL - Arithmetic overflow while working with milliseconds

I have a very simple script in TSQL that tries to convert a timestamp in milliseconds to a

data type. This also includes the local time offset.

SET @Time = 1413381394000
@Time - DATEDIFF(MILLISECOND, GETDATE(), GETUTCDATE()), CAST('1970-01-01 00:00:00' AS DATETIME)) AS [Datetime]

The error it's giving me all the time is:

Arithmetic overflow error converting expression to data type int.

Now, I don't have any explicit
variables in this query, and any
I've did, resulted in the same error.

Which is the wrong part in this query? Is
the default return data type of

I know that I could just divide
by 1000 and work with
instead of
, I just want to know if there is a way to work directly with milliseconds, since the idea is to use this script as an Inline Table-Valued Function (cannot use scalar ones for other reasons outside this query).

Answer Source

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))
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download