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;
精彩评论