SQL: Using DATEADD with bigints

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


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()))

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.


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.

Tags:

Sql

Sql Server 2008

Recent Posts