UDF For Extracting Actual Time From Two DateTime

----- First Step 

CREATE FUNCTION udf_total_time (@start_time datetime, @end_time datetime, @rtn_format_type char(1) )  
RETURNS char(20) AS   
BEGIN  

--------------------------------------------------------
-- Function Written By Gurtej Singh Pandher
-- Why This Function Required :--
-- Because i could not found any function in SQL Server 
-- Extract the actual time in (HH:MM:SS or HH.MM Format). 
---SQL Server Provide only function like 
---DATEDIFF which is not sufficiant.

---Suggestion :-
---Start DateTime and End DateTime Should not be Null.

RETURN (

case when @rtn_format_type='T' then ---- Then For Time Format (00:00:00)

case when @start_time is null then '  ' else ---- Check if Time is Null

----------------
case len(rtrim(ltrim(convert(char(2),((datediff(ss,@start_time,@end_time) / 60) / 60)) ))) 
when 2 then rtrim(ltrim(convert(char(2),((datediff(ss,@start_time,@end_time) / 60) / 60)) ))
else rtrim(ltrim('0'+convert(char(2),((datediff(ss,@start_time,@end_time) / 60) / 60)) ))
end +':'+

case len(rtrim(ltrim(convert(char(2),((datediff(ss,@start_time,@end_time) / 60) - (((datediff(ss,@start_time,@end_time) / 60) / 60) * 60) )) ))) 
when 2 then rtrim(ltrim(convert(char(2),((datediff(ss,@start_time,@end_time) / 60) - (((datediff(ss,@start_time,@end_time) / 60) / 60) * 60) )) ))
else rtrim(ltrim('0'+convert(char(2),((datediff(ss,@start_time,@end_time) / 60) - (((datediff(ss,@start_time,@end_time) / 60) / 60) * 60) )) ))
end+':'+

case len(rtrim(ltrim(convert(char(2),(datediff(ss,@start_time,@end_time) - ((( ((datediff(ss,@start_time,@end_time) / 60) / 60) * 60) * 60 ) + ( (((datediff(ss,@start_time,@end_time) / 60) - (((datediff(ss,@start_time,@end_time) / 60) / 60) * 60) )) * 60 ))))  )))
when 2 then rtrim(ltrim(convert(char(2),(datediff(ss,@start_time,@end_time) - ((( ((datediff(ss,@start_time,@end_time) / 60) / 60) * 60) * 60 ) + ( (((datediff(ss,@start_time,@end_time) / 60) - (((datediff(ss,@start_time,@end_time) / 60) / 60) * 60) )) * 60 )))) ))
else rtrim(ltrim('0'+convert(char(2),(datediff(ss,@start_time,@end_time) - ((( ((datediff(ss,@start_time,@end_time) / 60) / 60) * 60) * 60 ) + ( (((datediff(ss,@start_time,@end_time) / 60) - (((datediff(ss,@start_time,@end_time) / 60) / 60) * 60) )) * 60 )))) ))
end
----------------

end -- case for null value


else case when @rtn_format_type='N' then  ---- For Numeric Format (0.00)


case when @start_time is null then '0.00' else  ---- Check if Time is Null

-------------
case len(rtrim(ltrim(convert(char(2),((datediff(ss,@start_time,@end_time) / 60) / 60)) ))) 
when 2 then rtrim(ltrim(convert(char(2),((datediff(ss,@start_time,@end_time) / 60) / 60)) ))
else rtrim(ltrim('0'+convert(char(3),((datediff(ss,@start_time,@end_time) / 60) / 60)) ))
end +'.'+

case len(rtrim(ltrim(convert(char(2),((datediff(ss,@start_time,@end_time) / 60) - (((datediff(ss,@start_time,@end_time) / 60) / 60) * 60) )) ))) 
when 2 then rtrim(ltrim(convert(char(2),((datediff(ss,@start_time,@end_time) / 60) - (((datediff(ss,@start_time,@end_time) / 60) / 60) * 60) )) ))
else rtrim(ltrim('0'+convert(char(2),((datediff(ss,@start_time,@end_time) / 60) - (((datediff(ss,@start_time,@end_time) / 60) / 60) * 60) )) ))
end
----------------


end -- case for null value


end ---- End of Then For Time Format (00:00:00)
end ---- End of Then For Numeric Format (0.00)



)  

END  --- End OF First Tnen -- RETURN


GO


----- Second Step is Create SP 
----- Example Below :-

create procedure use_of_udf_total_time as 

set nocount on

---Here I am creating one example Stored Procedure
---for how we can use this function 

create table #temp (
srno int identity (1,1),
start_time datetime not null,
end_time datetime not null)

insert into #temp 
select getdate(),dateadd(hh,1,getdate())
union
select getdate(),dateadd(hh,2,getdate())
union
select getdate(),dateadd(hh,3,getdate())
union
select getdate(),dateadd(hh,4,getdate())
union
select getdate(),dateadd(hh,5,getdate())
union
select getdate(),dateadd(hh,6,getdate())
union
select getdate(),dateadd(hh,7,getdate())
union
select getdate(),dateadd(hh,8,getdate())
union
select getdate(),dateadd(hh,9,getdate())

--select *,dbo.udf_total_time(start_time,end_time,'N') as total_time from #temp


update #temp set end_time = dateadd(mi,10,end_time) where srno=1
update #temp set end_time = dateadd(mi,-10,end_time) where srno=2
update #temp set end_time = dateadd(mi,12,end_time) where srno=3
update #temp set end_time = dateadd(mi,33,end_time) where srno=4
update #temp set end_time = dateadd(mi,45,end_time) where srno=5
update #temp set end_time = dateadd(mi,-23,end_time) where srno=6
update #temp set end_time = dateadd(mi,16,end_time) where srno=7
update #temp set end_time = dateadd(mi,-11,end_time) where srno=8
update #temp set end_time = dateadd(mi,27,end_time) where srno=9


update #temp set end_time = dateadd(ss,12,end_time) where srno=1
update #temp set end_time = dateadd(ss,-11,end_time) where srno=2
update #temp set end_time = dateadd(ss,15,end_time) where srno=3
update #temp set end_time = dateadd(ss,34,end_time) where srno=4
update #temp set end_time = dateadd(ss,47,end_time) where srno=5
update #temp set end_time = dateadd(ss,-28,end_time) where srno=6
update #temp set end_time = dateadd(ss,17,end_time) where srno=7
update #temp set end_time = dateadd(ss,-19,end_time) where srno=8
update #temp set end_time = dateadd(ss,22,end_time) where srno=9


--- Here Three parameters you have to pass like 
----Start Time , End Time --- Both sould be DATETIME 
----Result Format 'T' Stands for Time Format 
----(HH:MM:SS), 'N' Stands for Numeric Format (HH.MM)

select *,dbo.udf_total_time(start_time,end_time,'N') as total_time_in_NUMERIC_FORMAT from #temp

select *,dbo.udf_total_time(start_time,end_time,'T') as total_time_in_TIME_FORMAT from #temp

go

--- Third Step (Last Step)

execute use_of_udf_total_time