How to select total time from three time columns (SQL Server 2008)

I have tabl开发者_如何学JAVAe with columns of time(0) datatypes:

Name   TimeOne          TimeTwo          TimeThree
------ ---------------- ---------------- ----------------
Sarah  06:45:00         03:30:00         NULL
John   06:45:00         NULL             NULL

How to make SELECT statement so that the "CalculatedTotal" column is total from TimeOne, TimeTwo and TimeThree per row?

What I'd like to have is select query like this:

SELECT
    Name,
    TimeOne,
    TimeTwo,
    TimeThree,
    TimeOne + TimeTwo + TimeThree As CalculatedTotal
FROM 
    MyTable

I'd like to get back resultset like this:

Name   TimeOne          TimeTwo          TimeThree        CalculatedTotal
------ ---------------- ---------------- ---------------- ---------------
Sarah  06:45:00         03:30:00         NULL             10:15:00
John   06:45:00         NULL             NULL             06:45:00

Just using plus operator in select statement gives you an error:

Operand data type time is invalid for add operator.

---

You could determine the number of seconds for each time value, add them together and convert back to time:

select TimeOne, TimeTwo, TimeThree,
       cast(dateadd(s, isnull(datediff(s, 0, TimeOne), 0) + isnull(datediff(s, 0, TimeTwo), 0) + isnull(datediff(s, 0, TimeThree), 0), 0) as time(0)) as CalculatedTotal
from MyTable

---

Try the below script.

select convert(time, CONVERT(datetime, '00:08:00.000') + CONVERT(datetime, '00:07:00.000'))

select convert(time,cast('00:08:00.000'as datetime)+cast('00:07:00.000' as datetime) ) as 'T'

---

I belive you can use the SUM() function

SELECT
    Name,
    TimeOne,
    TimeTwo,
    TimeThree,
    SUM(TimeOne+TimeTwo+TimeThree) As CalculatedTotal
FROM 
    MyTable

But equally you might need to convert each to seconds first using the SECOND() function, then use SEC_TO_TIME() on the result.

Edit: I've just had a look at the manual:

The SUM() and AVG() aggregate functions do not work with temporal values. (They convert the values to numbers, which loses the part after the first nonnumeric character.) To work around this problem, you can convert to numeric units, perform the aggregate operation, and convert back to a temporal value. Examples:

SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(time_col))) FROM tbl_name;
SELECT FROM_DAYS(SUM(TO_DAYS(date_col))) FROM tbl_name;