CODEWITHSUNDEEP
sqlsql-servert-sqlsum
awelpu
awelpu

Reputation: 1

Sum and convert time with t-sql

I'm trying to sum total time needed to complete all cases during one session. Time per case is stored in [time] column, in seconds. How to sum it and convert to the hh:mm:ss format?

My code looks like that:

[sessionno] as session_no
,count ([id]) as No_Items
,(convert (varchar, dateadd (ms, (sum([time])) * 1000, 0), 108)) as total_work_time

The result should look like:

session_no | No_items | total_work_time
112138          8         00:35:12
112583          0         00:00:00
212558          1         00:00:17

Instead of that I receive:

session_no | No_items | total_work_time
112138          8         1622
112583          0         null
212558          1         324

Upvotes: 0

Views: 228

Answers (1)

Radim Bača
Radim Bača

Reputation: 10701

Few corrections of your SQL

 SELECT [sessionno] as session_no,
        count ([id]) as No_Items,
        dateadd (ss, sum([time]), cast('00:00:00' as time)) as total_work_time
 FROM your_table
 GROUP BY [sessionno]

However, be aware that the time datatype may overflow.

Upvotes: 1

Related Questions