字符串转换,该如何解决

字符串转换
数据库中time 字段 为varchar 表示用了多长时间,(时：分：秒)
数据 有两种情况,一种前面四位，一种前面两位，如下举例


  time
0000:00:05.84------用了5.84秒
  null------还用时间可记0  
  00:10:05.84------用了10分5.84秒
0000:22:22.22------用了22分22.22秒
0000:11:12.09------用了11分12.09秒

要求 这列的时间和 求一共用了多少秒也可以

其实我要的是 平均时间，我只想到这样先求总时间/行数 当然 平均时间的结果如果也是这种格式就最好了 不知道如何实现，谢谢

[解决办法]
sum(datediff(ss,[time],'00:00:00'))
[解决办法]

SQL code

create table tb(time varchar(20))insert into tb values('0000:00:05.84')insert into tb values(null)insert into tb values('00:10:05.84')insert into tb values('0000:22:22.22')insert into tb values('0000:11:12.09')goselect sum(datediff(ss,'1900-01-01' , cast(substring(time,charindex(':',time) + 1 , len(time)) as datetime))) From tb/*            ----------- 157440（所影响的行数为 1 行）*/select avg(datediff(ss,'1900-01-01' , cast(substring(time,charindex(':',time) + 1 , len(time)) as datetime))) From tb/*            ----------- 39360（所影响的行数为 1 行）*/drop table tb
[解决办法]
SQL code
declare @t table (time varchar(13))insert into @tselect '0000:00:05.84' union allselect null union allselect '00:10:05.84' union allselect '0000:22:22.22' union allselect '0000:11:12.09'select  sum(isnull(datediff(ss, '1900-01-01',                            cast(right(time, 11) as datetime)), 0))from    @t/*2624*/
[解决办法]
SQL code
declare @t table (time varchar(13))insert into @tselect '0000:00:05.84' union allselect null union allselect '00:10:05.84' union allselect '0000:22:22.22' union allselect '0000:11:12.09'DECLARE @i INT select  @i=sum(isnull(datediff(ms, '1900-01-01',                            cast(right(time, 11) as datetime)), 0))from    @tSELECT LTRIM(@i/1000)+'.'+LTRIM(@i%1000)+'秒'/*2625.990秒*/SELECT LTRIM(@i/60000)+'分钟'+LTRIM(@i/60000)+'秒'+LTRIM(@i%1000)+'毫秒'/*43分钟43秒990毫秒*/