比如,有一张表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 数据类型作为参数。”错误信息。不知道该如何做了,请指点一下,谢谢了!
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 数据类型作为参数。”错误信息。不知道该如何做了,请指点一下,谢谢了!
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*/
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'
goselect convert(varchar(8),dateadd(ss,avg(datediff(ss,0,cast(doTime as datetime))),0),108) from tb /*
--------
11:38:00(1 row(s) affected)*/
--生成指定日期范围内的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
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 行)*/