Why ? I googled this and found a lot of solutions that did not work :(
So i just had to look into it.
I wanted the time difference to be displayed in the following format:
HHH:MM:SS (@type = 0)
Where HHH = Total number of hours, so if the difference is one week,
then this value will be 168. (168:00:00).
I also created another variant:
D MM:HH:SS (@type = 1)
Where D = Total number of days, so if the difference is one week,
then this value will be 7. (7 00:00:00).
create function [dbo].[TimeDiff](@date1 datetime, @date2 datetime, @type smallint) returns varchar(50) as begin declare @days int declare @totalhours int declare @hours int declare @minutes int declare @seconds int declare @ret varchar(50) set @seconds = datediff(ss, @date1,@date2) set @totalhours = @seconds / 3600 set @days = @totalhours / 24 set @hours = @totalhours - (@days * 24) set @minutes = ((@seconds - (3600 * @totalhours)) / 60) set @seconds = ((@seconds - (3600 * @totalhours) - (60 * @minutes))) if(@type = 0) begin --HHH:MM:SS set @ret = case when @totalhours < 10 then '0' + convert(varchar(20), @totalhours) else convert(varchar(20), @totalhours) end + ':' + right('0' + convert(varchar(20), @minutes), 2) + ':' + right('0' + convert(varchar(20), @seconds), 2) end else begin --D HH:MM:SS set @ret = convert(varchar(20), @days) + ' ' + right('0' + convert(varchar(20), @hours), 2) + ':' + right('0' + convert(varchar(20), @minutes), 2) + ':' + right('0' + convert(varchar(20), @seconds), 2) end return @ret end
No comments:
Post a Comment