Date and Time

These functions are for working with date and time values.

fn_convert_unix_to_date_time

This function converts a UNIX timestamp to a SQL datetime.

CREATE FUNCTION [DBO].[fn_convert_unix_to_date_time](
                                                 -- Unix timestamp to be converted
                                                     @Unix DECIMAL( 17, 7 ),
                                                     -- flag determining if local time zone,
                                                     -- or GMT should be returned
                                                     @asLocalTime_YN CHAR( 1 ) = 'N' )
RETURNS DATETIME2 AS
BEGIN
    DECLARE @result DATETIME = CONVERT( DATETIME, '01/01/1970' ),
        @UnixSeconds BIGINT,
        @UnixDecimal NVARCHAR( 8 ),
        @LocalTimeOffset BIGINT,
        @AdjustedLocalDatetime BIGINT,
        @MinIntValue INT,
        @MaxIntValue INT,
        @RemainingSeconds BIGINT;
    -- Get Unix seconds and decimal
    SET @UnixSeconds = FLOOR( @Unix );
    SET @UnixDecimal = RIGHT( CAST( @Unix - @UnixSeconds AS NVARCHAR( 9 ) ), 8 );
    -- if local timezone flag set,
    -- then adjust offset minutes for timezone,
    -- or 0 for GMT.
    SET @LocalTimeOffset = DATEDIFF( second, GETDATE(), GETUTCDATE() );
    -- define int limit
    SET @MinIntValue = -2147483648;
    SET @MaxIntValue = 2147483647;
    -- if local timezone flag set,
    -- then adjust offset minutes for timezone,
    -- or 0 for GMT.
    SET @AdjustedLocalDatetime =
    CASE COALESCE( @asLocalTime_YN, 'F' )
    WHEN 'Y' THEN
        @UnixSeconds - @LocalTimeOffset
        ELSE @UnixSeconds
    END;
    -- 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 value
    RETURN --SELECT DATEADD(second, @AdjustedLocalDatetime, @result)
    CONVERT( DATETIME2, CONVERT( NVARCHAR( 32 ), DATEADD( second, @AdjustedLocalDatetime, @result ), 120 ) + @UnixDecimal );
END;
GO

fn_friendly_elapsed_minutes

This function converts minutes into a text form of hours, minutes and seconds.

CREATE FUNCTION [dbo].[fn_friendly_elapsed_minutes](
                                                        @minutes INT )
RETURNS NVARCHAR( 127 ) AS
BEGIN
    DECLARE @string VARCHAR( 127 );
    SET @string =
    CASE
    WHEN @minutes < 60 THEN
        CAST( @minutes AS VARCHAR( 10 ) ) + ' Minute'
    WHEN @minutes < 1440 THEN
        CAST( @minutes / 60 AS VARCHAR( 10 ) ) + ' Hour, ' + CAST( @minutes % 60 AS VARCHAR( 10 ) ) + ' Minute'
        ELSE CAST( @minutes /( 1440 ) AS VARCHAR( 10 ) ) + ' Day, ' + CAST(( @minutes % 1440 ) / 60 AS VARCHAR( 10 ) ) + ' Hour, ' + CAST((( @minutes % 1440 ) % 60 ) AS VARCHAR( 10 ) ) + ' Minute'
    END;
    IF CHARINDEX( '1 Day', @string ) = 0
    BEGIN
        SET @string = REPLACE( @string, 'Day', 'Days' );
    END;
    IF CHARINDEX( '1 Hour', @string ) = 0
    BEGIN
        SET @string = REPLACE( @string, 'Hour', 'Hours' );
    END;
    IF CHARINDEX( '1 Minute', @string ) = 0
    BEGIN
        SET @string = REPLACE( @string, 'Minute', 'Minutes' );
    END;
    RETURN @string;
END;
GO