How to take table data hours, minutes and seconds

Question

I'm using following SQL query to take AvgTime, when pass two dates to fnDatetoSeconds its return time difference in seconds. in above query return output in minutes.

I need take it as HH.MM.SS . how can I do it

SELECT
ISNULL(CAST(AVG(CASE WHEN (l.Status = 5 OR l.Status = 6) THEN dbo.fnDatetoSeconds(l.AssistedTime, l.AddedTime, NULL)*1.0 ELSE NULL END)/60.00 AS DECIMAL(18,6)), 0) AS AvgTime
ISNULL(CAST(AVG(CASE WHEN (l.Status = 2) THEN dbo.fnDatetoSeconds(l.CompletedTime, l.AddedTime, NULL)*1.0 ELSE NULL END)/60.00 AS DECIMAL(18,6)), 0) AS AvgTimeNew

FROM @LobbyRecords l;

Does this answer your question? [How to convert Seconds to HH:MM:SS using T-SQL](https://stackoverflow.com/questions/1262497/how-to-convert-seconds-to-hhmmss-using-t-sql) — Sebastian Brosch, Nov 22 '19 at 11:27

Gordon Linoff · Answer 1 · 2019-11-23T14:18:21.320

0

This addresses the original question.

If you have two times and you want the average as a time, I would recommend:

select convert(time,
               dateadd(second,
                       avg( datediff(second, l.AddedTime, l.AssistedTime)),
                       0
                      )
              )
from @LobbyRecords l
where l.status in (5, 6);

I moved the case condition to a where clause just to simplify the logic to focus on key idea -- add the average number of seconds to 0.

edited Nov 23 '19 at 14:18

answered Nov 22 '19 at 11:36

Gordon Linoff

1,242,037
58
646
786

Sir, I need case statement, because I have separate columns inside that select statement, each one need separate case logic, so I can't use it inside where clause. so please give me an answer with case statement. – thomsan Nov 23 '19 at 08:55
please check my updated question, so you can take idea,what I'm saying. there have separate columns like that – thomsan Nov 23 '19 at 08:59
please help me sir – thomsan Nov 23 '19 at 12:57

How to take table data hours, minutes and seconds

1 Answers1