GooglePrettify

2019年4月17日 星期三

MSSQL convert int timestamp to datetime

CREATE FUNCTION dbo.fn_ToDT (@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;

Usage:
dbo.fn_ToDT([LastUpdateTime]) as [LastUpdateTime]

沒有留言:

張貼留言