How do I convert a float Value to Time in sql and get its sum?

Question

I have a column in table TotalHours which is of type float.

The value are as

3.4
1.3
4.2

the format the hour column is following is like hours.min

I want to get the total sum

the query which i am using is as

select sum(TotalHours) as hours from workdone where userid = 3

so it is returning 8.9 which is wrong, first i need to convert it to hours and then take the sum but i am stucked.

How do I get convert the values to hours and get sum?

Edited:The sum value will be in float format again. For eg, the sum result should be 9.3

it should return in the float format like if total is 3.2 ie 3 hour and 20 min — Hassaan, Nov 12 '14 at 09:39
How would you represent "one hour 10 minutes" using your notation? — zerkms, Nov 12 '14 at 09:39
basically at client side the value are converted to hours till 2 decimal places and then save to the field — Hassaan, Nov 12 '14 at 09:44
[Check This](https://stackoverflow.com/a/51769357/5096847) I hope ,It will solve your problem — Sanket Parchande, Aug 09 '18 at 14:18
You can check on the following Link [Click Here](https://stackoverflow.com/a/51769357/5096847) — Sanket Parchande, Aug 09 '18 at 14:19

GarethD · Answer 1 · 2014-11-12T10:37:36.330

This is not a good way to store duration. Storing decimal seconds, minutes, hours is fine, but it should be to base 10, i.e. 1 hour 30 minutes, should be 1.5 hours, or 90 minutes, not 1.3 - What if someone inserts 1.7 into your table? It is also much simpler to add/subtract data stored in seconds/minutes, and to reformat as required.

Anyway, you can do the conversion using some fairly simple logic. The hours portion will just be the portion before the decimal, i.e. the integer part of the number, this can be obtained by rounding your number down - e.g. FLOOR(1.3) = 1 hour. Then to get the number of minutes you need to extract the decimal part of the number and multiply by 100 - e.g. extract 0.3 from 1.3 and multiply this by 100 to get 30 minutes.

SELECT  [Hours] = FLOOR(n),
        [Minutes] = (n - FLOOR(n)) * 100,
        TotalMinutes = (FLOOR(n) * 60) + (n - FLOOR(n)) * 100,
        SummedMinutes = SUM((FLOOR(n) * 60) + (n - FLOOR(n)) * 100) OVER(),
        SummedHours = SUM((FLOOR(n) * 60) + (n - FLOOR(n)) * 100) OVER() / 60.0
FROM    (VALUES (3.4), (1.3), (4.2)) t (N);

Then if you need to convert 9.5 hours back to 9.3 (again, this is a terrible way of representing 9 hours 30 minutes) you would need to reverse the logic above, i.e. Total hours + (minutes / 100)

SELECT  [Hours] = CAST(FLOOR(SummedMinutes / 60) AS FLOAT),
        [Minutes] = SummedMinutes % 60,
        OddFormat = CAST(FLOOR(SummedMinutes / 60) AS FLOAT) + (SummedMinutes % 60 / 100)
FROM    (   SELECT  SummedMinutes = SUM((FLOOR(n) * 60) + (n - FLOOR(n)) * 100)
            FROM    (VALUES (3.4), (1.3), (4.2)) t (N)
        ) AS t;

Example on SQL Fiddle

I am curious as to why an answer that both correctly answers the question asked, and suggests an alternative method that would be better is *not useful* - Always happy to be proved wrong if the down voter cares to comment? — GarethD, Nov 12 '14 at 10:04

Veera · Answer 2 · 2014-11-12T10:59:32.373

Try this query to sum up hour and minutes:

 DECLARE @Result AS INT

 SELECT @Result = SUM(SubString(CAST(CAST(TOTALHOUR AS DECIMAL(10,2)) 
     AS VARCHAR(10)), 0, Charindex('.', CAST(CAST(TOTALHOUR AS DECIMAL(10,2)) 
     AS VARCHAR(10)))) * 60 + SubString(CAST(CAST(TOTALHOUR AS DECIMAL(10,2)) 
     AS VARCHAR(10)), Charindex('.', CAST(CAST(TOTALHOUR AS DECIMAL(10,2)) 
     AS VARCHAR(10))) + 1, len(CAST(CAST(TOTALHOUR AS DECIMAL(10,2)) 
     AS VARCHAR(10)))))
    FROM WORKDONE 
    WHERE userid = 3

SELECT @Result, CAST(@Result / 60 
       AS VARCHAR(10)) + ':' + CAST(@Result % 60 
       AS VARCHAR(10)) 
       AS ExpectedResult

ExpectedResult = 9:30

we are not here for spelling correction. English is not every one's national language — Hassaan, Nov 12 '14 at 10:16

Deep · Answer 3 · 2014-11-12T11:18:02.637

0

This should work :

SELECT  sum(CAST(TotalHours as int))
        + sum(cast(Parsename(cast((TotalHours) as decimal(10,2)),1) as int))/60 
        + (sum(cast(Parsename(cast((TotalHours) as decimal(10,2)),1) as int))%60) *0.01
FROM   workdone
WHERE  userid = 3

sql fiddle

edited Nov 12 '14 at 11:18

answered Nov 12 '14 at 10:14

Deep

3,162
1
12
21

This gives 8.9 for values in the question, not 9.3 as required. – GarethD Nov 12 '14 at 10:20
1

This [returns 8.9](http://sqlfiddle.com/#!3/818f7/2) when you use the data type float (as stated in the question) – GarethD Nov 12 '14 at 10:28
Updated answer so that it should be compatible with both decimal and float – Deep Nov 12 '14 at 11:02

score -2 · Answer 4 · edited Nov 12 '14 at 10:01

-2

You can use this kind of regexp to seperate digits and their deciamls and you can achieve the result. Below is the sample and you need to apply this for all rows and sum it up

 select (regexp_substr('4.2','[[:digit:]]',1)*60 + 
         replace(regexp_substr('4.2','\.[[:digit:]]'),'.')*10)/60 
  from dual;

edited Nov 12 '14 at 10:01

Saravana Kumar

3,669
5
15
35

answered Nov 12 '14 at 09:53

arunb2w

1,196
9
28

`regexp_substr` is not a SQL Server function. – GarethD Nov 12 '14 at 09:54

How do I convert a float Value to Time in sql and get its sum?

4 Answers4

Linked