GetElapsedTime from two DateTime2 values  

Posted by ReelTym

if object_id( 'dbo.GetElapsedTime' ) is not null drop function dbo.GetElapsedTime
go
create function dbo.GetElapsedTime
(
    @Start  DATETIME2(6),
    @End    DATETIME2(6)
)
RETURNS NVARCHAR(60)
AS
BEGIN
    DECLARE
        @years INT, @months INT, @days INT, @hours INT, @minutes INT, @seconds INT, @fractionalseconds INT, @temp DATETIME2(6), @return NVARCHAR(30),
        @StartNS INT = CONVERT( INT, RIGHT( CONVERT( NVARCHAR(60), @Start, 121 ), 6 ) ),
        @EndNS INT = CONVERT( INT, RIGHT( CONVERT( NVARCHAR(60), @End, 121 ), 6 ) )

    SELECT @days = CASE WHEN DATEDIFF( hour, @Start, @End ) > 23 THEN DATEDIFF( day, @Start, @End ) ELSE 0 END;         SELECT @temp = DATEADD( day, @days, @Start )
    SELECT @hours = CASE WHEN DATEDIFF( minute, @temp, @End ) > 59 THEN DATEDIFF( hour, @temp, @End ) ELSE 0 END;       SELECT @temp = DATEADD( hour, @hours, @temp )
    SELECT @minutes = CASE WHEN DATEDIFF( second, @temp, @End ) > 59 THEN DATEDIFF( minute, @temp, @End ) ELSE 0 END;   SELECT @temp = DATEADD( minute, @minutes, @temp )
    SELECT @seconds = DATEDIFF( second, @temp,  @End ) + CASE WHEN @StartNS > @EndNS THEN -1 ELSE 0 end ;
    SELECT @fractionalseconds = CASE WHEN @StartNS > @EndNS THEN 1000000 - @StartNS + @EndNS ELSE @EndNS - @StartNS END

    SELECT @return =
        CASE WHEN @days > 0 then CONVERT( NVARCHAR, @days ) + N'd ' ELSE N'' END +
        RIGHT( N'0' + CONVERT( NVARCHAR, @hours ), 2 ) + N':' +
        RIGHT( N'0' + CONVERT( NVARCHAR, @minutes ), 2 ) + N':' +
        RIGHT( N'0' + CONVERT( NVARCHAR, @seconds ), 2 ) + N'.' +
        RIGHT( N'00000' + CONVERT( NVARCHAR, @fractionalseconds ), 6 )

    RETURN @return
END
go
select Elapsed = dbo.GetElapsedTime( '2000-01-01 00:00:00.000000', '2000-12-31 23:59:59.999999' )
/*
Elapsed
------------------------------
365days 23:59:59.999999
*/
select Elapsed = dbo.GetElapsedTime( '2001-01-01 00:00:00.000000', '2001-12-31 23:59:59.999999' )
/*
Elapsed
------------------------------
364days 23:59:59.999999
*/
select Elapsed = dbo.GetElapsedTime( '2001-12-31 23:59:59.999999', '2002-01-01 00:00:00.000000' )
/*
Elapsed
------------------------------
00:00:00.000001
*/
select Elapsed = dbo.GetElapsedTime( '2002-01-01 00:00:00.000000', '2002-02-02 02:02:02.000002' )
/*
Elapsed
------------------------------
00:00:00.000001
*/
go