對比加班時間和考勤時間,得出實際加班時長函数

CREATE function fun_att_wot_realtime(@efdate1 as datetime, @efdate2 as datetime, @att_ot_in as varchar(5), @att_ot_out as varchar(5))
returns varchar(100)
--對比加班時間和考勤時間,得出實際加班時長
begin
 if @att_ot_in is null and @att_ot_out is null
  return '0'
 declare @d_start datetime
 declare @d_end datetime
 declare @d_len nvarchar(4)
 declare @d1 as varchar(10)
 declare @d2 as varchar(10)
 set @d1 = convert(varchar(10), @efdate1, 120)
 set @d2 = convert(varchar(10), @efdate2, 120)
 declare @dt_att_ot_in datetime
 declare @dt_att_ot_out datetime
 set @dt_att_ot_in = @d1 + ' ' + @att_ot_in
 set @dt_att_ot_out = @d2 + ' ' +  @att_ot_out
 if @dt_att_ot_in > @efdate1
  set @d_start = @dt_att_ot_in
 else  
  set @d_start = @efdate1
 if @dt_att_ot_out < @efdate2 or @efdate2 = @efdate1
  set @d_end = @dt_att_ot_out
 else  
  set @d_end = @efdate2
 declare @m int
 set @m = DATEDIFF(minute, @d_start, @d_end)
 return cast((cast(@m as float) / cast(60 as float)) as numeric(9,2))
end
select dbo.fun_att_wot_realtime('2008-08-27 18:00:00', '2008-08-27 21:00:00', '17:48', '21:02')