Implementation #1
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 |
CREATE function [dbo].[fGetElapsedTime](@datetime1 datetime, @datetime2 datetime) returns nvarchar(25) as begin declare @default nvarchar(25) set @default = '00.00:00:00'; if (@datetime1 is null or @datetime2 is null) return null; if (@datetime1 = @datetime2) return @default; declare @neg bit set @neg = 0 if(@datetime1 > @datetime2) begin declare @dt datetime set @dt = @datetime1 set @datetime1 = @datetime2 set @datetime2 = @dt set @neg = 1 end declare @elapsed_time datetime declare @elapsed_days int declare @elapsed_hours int declare @elapsed_minutes int declare @elapsed_seconds int -- Get elapsed time as the difference between 2 datetimes select @elapsed_time = @datetime2-@datetime1 select @elapsed_days = datediff(d,@datetime1,@datetime2) select @elapsed_hours = datepart(hour,@elapsed_time) select @elapsed_minutes = datepart(minute,@elapsed_time) select @elapsed_seconds = datepart(second,@elapsed_time) declare @result nvarchar(50) if(@elapsed_days = 0 and @elapsed_hours = 0 and @elapsed_minutes = 0 and @elapsed_seconds = 0) return @default set @result = case when @elapsed_days < 10 then '0' + convert(varchar(20),@elapsed_days) + '.' else convert(varchar(20),@elapsed_days) + '.' end + case when @elapsed_hours < 10 then '0' + convert(varchar(20),@elapsed_hours) + ':' else convert(varchar(20),@elapsed_hours) + ':' end + case when @elapsed_minutes < 10 then '0' + convert(varchar(20),@elapsed_minutes) + ':' else convert(varchar(20),@elapsed_minutes) + ':' end + case when @elapsed_seconds < 10 then '0' + convert(varchar(20),@elapsed_seconds) else convert(varchar(20),@elapsed_seconds) end return case when @neg=0 then @result else '- ' + @result end end |
#1 Results
|
1 2 3 4 5 |
select [ElapsedTime] = [dbo].[fGetElapsedTime](dateadd(hh,-5,dateadd(n,-25,getdate())),getdate()) --00.05:25:00 select [ElapsedTime] = [dbo].[fGetElapsedTime](dateadd(d,-5,dateadd(hh,-3,getdate())),getdate()) --05.03:00:00 |
Implementation #2
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
CREATE function [dbo].[fGetElapsedTimeX] ( @dt1 datetime ,@dt2 datetime ) returns nvarchar(20) as begin declare @result nvarchar(20) set @result = convert(nvarchar(20), convert(varchar(10), convert(int, convert(float,@dt2) - convert(float, @dt1) ) * 24 /* hours over 24 */ + datepart(hh, @dt2 - @dt1) /* hours */ ) + ':' + right('0' + convert(varchar(2), datepart(mi, @dt2 - @dt1)), 2) /* minutes */ + ':' + right('0' + convert(varchar(2), datepart(ss, @dt2 - @dt1)), 2) /* seconds */ ) return @result end |
#2 Results
|
1 2 3 4 5 |
select [ElapsedTime] = [dbo].[fGetElapsedTimeX](dateadd(hh,-5,dateadd(n,-25,getdate())),getdate()) --5:25:00 select [ElapsedTime] = [dbo].[fGetElapsedTimeX](dateadd(d,-5,dateadd(hh,-3,getdate())),getdate()) --123:00:00 |