converting minutes to hh:mm:ss in sql

[jaseemibs001]

I have query like this:

select SUM(DATEDIFF(MI,t.Paydate,t.DelDate)) as sum_min,
       AVG( CONVERT(NUMERIC(18,2), DATEDIFF(MI,t.Paydate,t.DelDate) ) ) as avg_min
from Transaction_tbl t where t.transactID in(24,25)
group by t.vtid

I am getting out put like this:

sum_min     avg_min
----------- ---------------------------------------
26          26.000000
7           7.000000

now am getting sum_min out put in minutes,insted of geting out put in minutes i want to show in HH:mm:ss,Expect ed out put like this:

sum_min     avg_min
----------- ---------------------------------------
00:26:00          26.000000
00:07:00           7.000000

SO i tryed query like this:

SELECT convert(varchar(10),sum(DATEDIFF(hour,t.Paydate,t.DelDate)))+':' 
      +convert(varchar(10),sum(DATEDIFF(minute,t.Paydate,t.DelDate)% 60)) + ':' 
      +convert(varchar(10),sum(DATEDIFF(SECOND,t.Paydate,t.DelDate)% 60)) 
       AS ' HH:MM:SS'
FROM Transaction_tbl t 
 
WHERE t.transactID in(24,25) group by vtid

..so am getting out put like this:

 HH:MM:SS
--------------------------------
1:26:36
1:7:25

showing 1 hr extra. how i can get proper answer

[Rabbit]

I don't see what this has to do with .net. Let us know what SQL database server you're using and we can move it to the correct forum.

Please use code tags when posting code or formatted data.

That's because the datediff function rounds up. If you want it to be accurate down to the second, you need to get the difference in seconds for hours and minutes and use integer division.