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