SQL Server can convert a time-only portion of a datetime value from string to datetime, however in your example, you have a precision of 4 decimal places. SQL Server 2005 only recognizes 3 places. Therefore, you will need to truncate the right-most character:
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!
This will convert valid values into a DATETIME starting on 1900-01-01. SQL Server calculates dates based on 1 day = 1 (integer). Portions of days are then portions of the value 1 (i.e. noon is 0.5). Because a date was not specified in the conversion, SQL Server assigned the value of 0 days (1900-01-01), which accommodates our need to average the time portion.
To perform an AVG operation on a DATETIME, you must first convert the DATETIME to a decimal value, perform the aggregation, then cast back. For example
SELECT CAST(AVG(CAST(CAST(LEFT(TimeField, 12) as datetime) AS FLOAT)) AS DATETIME) FROM #TableWithTimeValues
--1900-01-01 05:00:00.000
If you need to store this with an extra decimal place, you can convert the DATETIME to a VARCHAR with time portion only and pad the string back to 13 characters:
SELECT CONVERT(VARCHAR, CAST(AVG(CAST(CAST(LEFT(TimeField, 12) as datetime) AS FLOAT)) AS DATETIME), 114) + '0' FROM #TableWithTimeValues