SUM hours in sql server

asma gh 21 Reputation points
2020-11-26T19:14:32.557+00:00

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

43065-hour.png

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

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,552 questions
0 comments No comments
{count} votes

Accepted answer
  1. MelissaMa-MSFT 24,176 Reputation points
    2020-11-27T02:20:14.417+00:00

    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

    1 person found this answer helpful.
    0 comments No comments

3 additional answers

Sort by: Most helpful
  1. Viorel 112.1K Reputation points
    2020-11-26T19:18:31.707+00:00

    Try this fix:

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

    0 comments No comments

  2. asma gh 21 Reputation points
    2020-11-26T19:36:26.777+00:00

    i'm sorry but i don't get the right result here is my querry and the result 43000-hourss.png


  3. asma gh 21 Reputation points
    2020-11-27T15:16:41.59+00:00

    Thankk you very muchhh , it worksss :D

    0 comments No comments