SQL: Using DATEADD with bigints

Question

I have some SQL to convert javascript dates to SQL dates which works great. However, I've encoutered some data which is too large and is causing an exception:

Arithmetic overflow error converting expression to data type int

Here is the SQL in question:

  DATEADD(MILLISECOND, cast(569337307200000 as bigint) % 1000, DATEADD(SECOND, cast(569337307200000 as bigint) / 1000, '19700101'))

I am running this on SQL Server 2008.

Answer 1

Just do the problematic DATEADD in two steps, starting with a coarser time unit (seconds, minutes, hours etc.), then dropping back to the fine grained one for the remainder.

Avoid going to the level of weeks and months though as that would require actual calendar calculations and we would prefer the system to handle that.

Example below needs to calculate a start time given a (possibly) large current duration in milliseconds.

-- large durations can overflow the integer argument needed for DATEADD

-- so do as two steps subtracting minutes (60000ms) and then remaining milliseconds.

DATEADD(ms, -large_duration_ms%60000, DATEADD(minute, -large_duration_ms/60000, GETDATE()))

Answer 2

One way I got around the Integer overflow issue was to subtract a more recent date from the microtime unix time stamp.

DATEADD(s, (CreationTimeStamp/1000-1384128000), '2013-11-11') AS CreateDate,

This will not fix the OP's problem because they will still overflow the max on the date column.

Answer 3

According to MSDN, in DATEADD (datepart , number , date )

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.

Also notice that even if you give number as an integer, depending on your date & datepart, it could overflow the max range of the date which is 31-12-9999 for sql server 2008

Number has to be an integer. Here is a Test Demo

Answer 4

I had the same problem and I wanted to be meet the datetime range of mssql

• Minimun datetime: 1753-01-01 00:00:00.000 (-6847804800)
• Maximum datetime: 9999-12-31 23:59:59.997 (253402300799)

To achieve this the only solution I found was to loop to use DATEADD with int range values.

So based on this answer: https://stackoverflow.com/a/2904294/687490

CREATE FUNCTION dbo.fn_ConvertToBigDateTime (@Datetime BIGINT)
RETURNS DATETIME
AS
BEGIN
    DECLARE @result datetime = Convert(datetime, '01/01/1970');

    DECLARE @LocalTimeOffset BIGINT
           ,@AdjustedLocalDatetime BIGINT
           ,@MinIntValue INT
           ,@MaxIntValue INT
           ,@RemainingSeconds BIGINT;

    -- define int limit
    SET @MinIntValue = -2147483648;
    SET @MaxIntValue = 2147483647;

    -- compute the datetime with the offset
    SET @LocalTimeOffset = DATEDIFF(second,GETDATE(),GETUTCDATE())
    SET @AdjustedLocalDatetime = @Datetime - @LocalTimeOffset

    -- going to the future
    WHILE(@AdjustedLocalDatetime>@MaxIntValue)
    BEGIN
        SET @AdjustedLocalDatetime = @AdjustedLocalDatetime - @MaxIntValue;
        SELECT @result = Convert(datetime, dateadd(ss, @MaxIntValue,@result));
    END

    -- going back in the past
    WHILE(@AdjustedLocalDatetime<@MinIntValue)
    BEGIN
        SET @AdjustedLocalDatetime = @AdjustedLocalDatetime - @MinIntValue;
        SELECT @result = Convert(datetime, dateadd(ss, @MinIntValue,@result));
    END

    RETURN (SELECT DATEADD(second,@AdjustedLocalDatetime, @result))
END;

You can then test the function with :

select dbo.fn_ConvertToBigDateTime(-6847804800) as 'min datetime', 
dbo.fn_ConvertToBigDateTime(253402300799) as 'max datetime'

Hope it will help.

Answer 5

You can try converting the millis to days, add the days to beginning of EPOCH, and add the ms part to the date at the end. The problem is that you were trying to convert millis to seconds, which can still be too large number for INT for larger dates.

DATEADD(MILLISECOND, 
  CAST(myLongDateMs AS BIGINT) % 86400000, 
  DATEADD(day, 
    CAST(myLongDateMs AS BIGINT) / 86400000, 
    '19700101'
  )
) 

Answer 6

I faced this problem too. In my sql statement, the error occurred when the date time value is null.

My solution is to check whether the date time value is null using "CASE When". Only running the arithmetic when it is not null, and the problem solved.