CODEWITHSUNDEEP
sqlsql-server-2008time
Orson DCunha
Orson DCunha

Reputation: 71

SQL Server adding two time columns in a single table and putting result into a third column

I have a table containing two time columns like this:

Time1       Time2  
07:34:33    08:22:44

I want to add the time in both these columns and put the result of addition into a third column may be Time3

Any help would be appreciated..Thanks

Upvotes: 4

Views: 11226

Answers (3)

Tanner Mork
Tanner Mork

Reputation: 1

declare @Time1 time
declare @Time2 time

set @Time1 = '07:34:33'
set @Time2 = '08:22:44'

select convert(time,convert(datetime,@Time1)+convert(datetime,@Time2)) as TimeResult

-- Result ------------------------------------
TimeResult
15:57:17.0000000

Upvotes: 0

S. Rojak
S. Rojak

Reputation: 454

The engine doesn't understand addition of two time values, because it thinks you can't add two times of day. You get:

Msg 8117, Level 16, State 1, Line 8
Operand data type time is invalid for add operator.

If these are elapsed times, not times of day, you could take them apart with DATEPART, but in SQL Server 2008 you will have to use a CONVERT to put the value back together, plus have all the gymnastics to do base 60 addition.

If you have the option, it would be best to store the time values as NUMERIC with a positive scale, where the unit of measure is hours, and then break them down when finally reporting them. Something like this:

DECLARE
    @r NUMERIC(7, 5);

SET @r = 8.856;

SELECT FLOOR(@r) AS Hours, FLOOR(60 * (@r - FLOOR(@r))) AS Minutes, 60 * ((60 * @r) - FLOOR(60 * @r)) AS Seconds

Returns

Hours    Minutes    Seconds
8        51         21.60000

There is an advantage to writing a user-defined function to do this, to eliminate the repeated 60 * @r calculations.

Upvotes: 0

jpw
jpw

Reputation: 44871

If the value you expect as the result is 15:57:17 then you can get it by calculating for instance the number of seconds from midnight for Time1 and add that value to Time2:

select dateadd(second,datediff(second,0,time1),time2) as Time3 
from your_table

I'm not sure how meaningful adding two discrete time values together is though, unless they are meant to represent duration in which case the time datatype might not be the best as it is meant for time of day data and only has a range of 00:00:00.0000000 through 23:59:59.9999999 and an addition could overflow (and hence wrap around).

If the result you want isn't 15:57:17 then you should clarify the question and add the desired output.

Upvotes: 10

Related Questions