09 February, 2012

Formatted TimeDiff UDF for MSSQL

I just created a UDF function for displaying a formatted time diff value in MSSQL.
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