mordack550 mordack550 - 5 months ago 73
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

DATETIME
data type. This also includes the local time offset.

DECLARE @Time AS BIGINT
SET @Time = 1413381394000
SELECT DATEADD(MILLISECOND,
@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
int
variables in this query, and any
CAST()
to
BIGINT
or
DECIMAL(13,0)
I've did, resulted in the same error.

Which is the wrong part in this query? Is
int
the default return data type of
DATEDIFF()
?

I know that I could just divide
@Time
by 1000 and work with
SECONDS
instead of
MILLISECONDS
, 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

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:

number
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))
Comments