sql 2005 求平均上班时间点

USE T_SQL;
GO
CREATE FUNCTION dbo.get_avg_time
 (@b_time datetime,@e_time datetime,@clerk varchar(50))
RETURNS @avg_time table
(
  clerk varchar(50),
  avg_time varchar(8)
)
AS
  BEGIN
   insert into @avg_time
   select clerk,convert(varchar(8),dateadd(ss,time,108),108)
   from
   (select clerk,
   avg(substring(convert(varchar(19),d_t,120), 12, 2) * 3600 +
      substring(convert(varchar(19),d_t,120), 15, 2) * 60 +
      substring(convert(varchar(19),d_t,120), 18, 2))
   as time
   from login_log
    where d_t between @b_time and @e_time
    and clerk = @clerk
    group by clerk )as tt
    return
END
GO

测试:
 

    insert into login_log
    values ('一哥','2010-10-02 08:20:00');
        insert into #tt
    values ('一哥','2010-10-03 08:10:00');
    insert into #tt
    values ('一哥','2010-10-04 08:30:00');
    insert into #tt
    values ('吴哥','2010-10-04 08:10:00');
        insert into #tt
    values ('吴哥','2010-10-05 08:20:00');
    insert into #tt
    values ('吴哥','2010-10-06 08:15:00');

    select * from dbo.get_avg_time('2010-10-02 08:20:00',getdate(),'吴哥')