Skip to main content

This SQL Server function returns the difference between dates (duration) in H:MM:SS format.

-- ==========================================================================
-- This function returns the difference between dates in the H:MM format.
--
-- Usage:
--
--     declare @StartDateTime datetime = dateadd(d, -1, getdate());
--     declare @EndDateTime datetime = getdate();
--     select dbo.GetTimeSpan(@StartDateTime, @EndDateTime, 'N/A');
--     >> 24:00
--
-- Based on: http://stackoverflow.com/a/13867469
-- ==========================================================================

create function dbo.GetTimeSpan(
    @StartDateTime datetime,
    @EndDateTime datetime,
    @DefaultValue varchar(25)
)
returns varchar (25)
as
begin
    --
    -- null checks
    if @StartDateTime is null
        begin
            return @DefaultValue;
        end;
    if @EndDateTime is null
        begin
            return @DefaultValue;
        end;

    --
    -- don't return negative timespans (ie: start date is after end date):
    if @StartDateTime > @EndDateTime
        begin
            return @DefaultValue;
        end;

    declare @result as varchar (25);

    select @result =
        cast(
            (cast
                (
                    cast(@EndDateTime as float) - cast(@StartDateTime as float) as int) * 24 /* hours over 24 */
                )
                    + datepart(hh, @EndDateTime - @StartDateTime) as varchar(10) /* hours */
            )
        + ':' + right('0' + cast(datepart(mi, @EndDateTime - @StartDateTime) as varchar(2)), 2) /* minutes */
        + ':' + right('0' + cast(datepart(ss, @EndDateTime - @StartDateTime) as varchar(2)), 2) /* seconds */

    return @result;
end