Calculate average of time difference between two dates

Question

i want to Calculate average and sum of time difference between two dates in DD:HH:MM:YY format of multiple records,

i tried this 

 

 

WITH cte AS

(

SELECT

wt.workorder_transaction_id,

wt.workorder_id,

wt.updated_at,

wt.status_id

,ROW_NUMBER ( )OVER (PARTITION BY workorder_id ORDER BY workorder_transaction_id ) AS rowno

FROM workorder_transaction wt

INNER JOIN workorder_status ws ON ws.status_id = wt.status_id

WHERE LOWER(ws.status) = 'qc pending'

AND workorder_id IN(

SELECT workorder_id FROM workorder_transaction wt

INNER JOIN workorder_status ws ON ws.status_id = wt.status_id

WHERE LOWER(ws.status) ='qc approved'

)

)

,cte1 AS

(

SELECT

STUFF(CONVERT(VARCHAR(50),wt.updated_at-cte.updated_at,114),1,2,DATEDIFF(hh,0,wt.updated_at-cte.updated_at))

AS TimeDiff

FROM cte

INNER JOIN workorder_transaction wt ON wt.workorder_id = cte.workorder_id

WHERE rowno = 1 And wt.status_id = 6

GROUP BY cte.workorder_transaction_id,cte.workorder_id,cte.updated_at,cte.status_id

,wt.status_id,wt.updated_at

)

SELECT

CONVERT(CHAR(8),DATEADD(SECOND,SUM ( DATEPART(hh,(CONVERT(DATETIME,TimeDiff,1))) * 3600 +

DATEPART(mi, (CONVERT(DATETIME,TimeDiff,1))) * 60 + DATEPART(ss,(CONVERT(DATETIME,TimeDiff,1)))),0),108)

AS total_sum,

CONVERT(CHAR(8),DATEADD(SECOND,AVG ( DATEPART(hh,(CONVERT(DATETIME,TimeDiff,1))) * 3600 +

DATEPART(mi, (CONVERT(DATETIME,TimeDiff,1))) * 60 + DATEPART(ss,(CONVERT(DATETIME,TimeDiff,1)))),0),108)

AS average

FROM cte1

 

 

when hours get above 24hr SQL server not handle the situation and give error as

"The conversion of a varchar data type to a datetime data type resulted in an out-of-range value."

 

can you please provide the solution