SQL Sever 2005中的平均时间值

我在SQL Sever 2005中有一个varchar字段,它以"hh:mm"ss.mmmm"格式存储时间值.

我真正想要做的是使用这些时间值的内置聚合函数取平均值.但是,这个:

SELECT AVG(TimeField) FROM TableWithTimeValues

不起作用,因为(当然)SQL不会平均varchars.但是,这个

SELECT AVG(CAST(TimeField as datetime)) FROM TableWithTimeValues

也行不通.就像我所知,SQL不知道如何将只有时间和没有日期的值转换为datetime字段.我已经尝试了各种各样的东西来让SQL将该字段转换为日期时间,但到目前为止,没有运气.

有谁能建议更好的方法？

SQL Server可以将日期时间值的仅时间部分从字符串转换为日期时间,但在您的示例中,您具有4个小数位的精度.SQL Server 2005只识别3个位置.因此,您需要截断最右侧的字符:

create table #TableWithTimeValues
(
    TimeField varchar(13) not null
)

insert into #TableWithTimeValues
select '04:00:00.0000'
union all
select '05:00:00.0000'
union all
select '06:00:00.0000'

SELECT CAST(TimeField as datetime) FROM #TableWithTimeValues
--Msg 241, Level 16, State 1, Line 1
--Conversion failed when converting datetime from character string.

SELECT CAST(LEFT(TimeField, 12) as datetime) FROM #TableWithTimeValues
--Success!

这将从1900-01-01开始将有效值转换为DATETIME.SQL Server根据1天= 1(整数)计算日期.然后,部分天数是值1的一部分(即中午是0.5).由于转换中未指定日期,因此SQL Server分配了0天(1900-01-01)的值,这符合我们平均时间部分的需要.

要在DATETIME上执行AVG操作,必须先将DATETIME转换为十进制值,执行聚合,然后再回送.例如

SELECT CAST(AVG(CAST(CAST(LEFT(TimeField, 12) as datetime) AS FLOAT)) AS DATETIME) FROM #TableWithTimeValues
--1900-01-01 05:00:00.000

如果您需要使用额外的小数位存储它,您可以将DATETIME转换为仅包含时间部分的VARCHAR,并将字符串填充回13个字符:

SELECT CONVERT(VARCHAR, CAST(AVG(CAST(CAST(LEFT(TimeField, 12) as datetime) AS FLOAT)) AS DATETIME), 114) + '0' FROM #TableWithTimeValues

试试这个

AVG(CAST(CAST('1900-01-01 ' + TimeField AS DateTime) AS Float))

你真的应该将它们存储在datetime列中.只需对该部分使用一致的日期(1/1/1900非常常见).然后你可以调用AVG()而不用担心它.