请问：用sql语句如何统计处datetime字段的平均值？

[zhenghaibinone]

比如，有一张表t，内容如下：
workerId doTime
201 10:47:00
203 13:39:00
208 09:33:00
201 13:38:00
204 09:08:00
206 13:03:00
…… ……

我想统计出doTime字段的平均值，用select avg(doTime) from t,提示：“average aggregate 运算不能以 datetime 数据类型作为参数。”错误信息。不知道该如何做了，请指点一下，谢谢了！

[SQL77]

结果想要啥

[lds1ove]

[Mr_Nice]

--> 测试数据：[TB]
if object_id('[TB]') is not null drop table [TB]
create table [TB]([workerId] int,[doTime] datetime)
insert [TB]
select 201,'10:47:00' union all
select 203,'13:39:00' union all
select 208,'09:33:00' union all
select 201,'13:38:00' union all
select 204,'09:08:00' union all
select 206,'13:03:00'

select * from [TB]

select dateadd(ss,avg(datediff(ss,'1900-01-01 00:00:00.000',dotime)),'1900-01-01') from tb 

/*
1900-01-01 11:38:00.000*/

[Mr_Nice]

瞎猜了一个。赫赫。

[rmljoe]

if object_id('tb') is not null
  drop table tb
go
create table tb(workerId int,doTime varchar(8))
insert into tb select 201,'10:47:00'  
     union all select 203,'13:39:00'  
     union all select 208,'09:33:00'  
     union all select 201,'13:38:00'  
     union all select 204,'09:08:00'  
     union all select 206,'13:03:00'
go

select convert(varchar(8),dateadd(ss,avg(datediff(ss,0,cast(doTime as datetime))),0),108) from tb 

/*
--------
11:38:00

(1 row(s) affected)

*/

[feilniu]

--生成指定日期范围内的100个随机日期（精确到秒）
DECLARE @BDate datetime, @EDate datetime
SET @BDate = '20090101'          --下限
SET @EDate = '20101231 23:59:59' --上限
SELECT n, random_datetime = DATEADD(second,ABS(CHECKSUM(NEWID()))%DATEDIFF(second,@BDate,@Edate),@BDate)
INTO #tb
FROM dbo.Nums
WHERE n BETWEEN 1 AND 100
--计算日期的平均值
SELECT
	avg1 = CAST(AVG(CAST(random_datetime AS decimal(18,9))) AS datetime),
	avg2 = DATEADD(second,AVG(CAST(DATEDIFF(second,'20090101',random_datetime) AS bigint)),'20090101') --这种方法可能要担心整数是否溢出
FROM #tb

[chuifengde]

DECLARE @a TABLE(workerId INT, doTime VARCHAR(20))
insert @a select 201 ,'10:47:00'
UNION ALL SELECT 203 ,'13:39:00'
UNION ALL SELECT 208 ,'09:33:00'
UNION ALL SELECT 201 ,'13:38:00'
UNION ALL SELECT 204 ,'09:08:00'
UNION ALL SELECT 206 ,'13:03:00'

SELECT CONVERT(VARCHAR(10),DATEADD(s,avg(DATEDIFF(s,'00:00:00',doTime)),'00:00:00'),108)
FROM @a 

--result
/*---------- 
11:38:00

（所影响的行数为 1 行）*/