SUM hours in sql server

Question

Hello , please i tried to make a sum on hours and minute when i convertet to decimal i don't get same result

here the exemple in excel sheet

and here the function that i used in sql server

sum(cast(datepart(hour, [HOUR]) + (datepart(minute, [HOUR])) / 100.00 as decimal(5, 2)))

any idea please how to fix this by function in sql server to get same result 23:38

thank you

Answer 1

Hi @asma gh ,

Thank you so much for posting here.

Please refer below and check whether it is helpful to you.

declare @temp table   
(  
groupid int,  
[hour] datetime  
)  
  
insert into @temp values  
(1,'2020-01-01 04:38:00'),  
(1,'2020-01-01 00:25:00'),  
(1,'2020-01-01 07:35:00'),  
(1,'2020-01-01 00:35:00'),  
(1,'2020-01-01 04:21:00'),  
(1,'2020-01-01 03:04:00'),  
(1,'2020-01-01 03:00:00'),  
(2,'2020-01-01 01:00:00'),  
(2,'2020-01-01 03:45:00'),  
(2,'2020-01-01 07:44:00')  
  
;with cte as (  
select groupid,sum(cast(datepart(hour, [HOUR]) + (datepart(minute, [HOUR])) / 60.00 as money)) sum   
from @temp  
group by groupid  
)  
SELECT groupid,CAST(CONVERT(VARCHAR, CONVERT(INT, Floor(sum)))   
       + '.'   
       + CONVERT (VARCHAR, CONVERT(INT, ROUND((sum - Floor(sum)) * 60.0,2))) as decimal(38, 2)) sum   
	   from cte  

Output:

groupid	sum  
1	23.38  
2	12.29  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Hot issues November--What can I do if my transaction log is full?
Hot issues November--How to convert Profiler trace into a SQL Server table

Answer 2

Thankk you very muchhh , it worksss :D

Answer 3

i'm sorry but i don't get the right result here is my querry and the result

Answer 4

Try this fix:

sum(cast(datepart(hour, [HOUR]) + (datepart(minute, [HOUR])) / 60.0 as decimal(5, 2)))