I've got a table with some datetime fields in it.

One field (call it field 1) is of the form mm/dd/yyyy and the other two
(fields 2 and 3) are in the form of hh:mm:ss:xx where xx is hundreths
of a second.

I'm getting the difference between field 2 and 3 using (datediff(ms,
access_time, release_time )/1000/60.). This seems to work fine.

However, in some other cases I'd like to add field 1 to field 2 and
then manipulate the result. This is where it gets weird.

If I do it like this: Convert(varchar 20),record_date+access_time,100),
it adds field 2 ok but subtracts two days. So for example 3/1/05 +
10:30:00 AM = 2/27/05 10:30:00 AM. So it effectively subtracts two
additional days for no apparent reason. If is use
record_date+2+access, then this returns the correct answer.

If I try to use Convert(varchar(20),dateadd(ss,record_date,
access_time),100) sql server complains Argument data type datetime is
invalid for argument 2 of the dateadd function.

Basically I'd just like to know how to add and subtract fields 1
(mm/dd/yyyy format) and 2 (hh:mm:ss:xx format).

As a bonus question, is it possible to get an average time for several
different times? For example the average time between 10:30 and 11:00
would be 10:45.

regards,
-David