• Status: Solved
• Priority: Medium
• Security: Public
• Views: 4276

# SQL Query - convert decimal to hours and minutes

I have a query:-

``````select SUM(timetaken)
from dbo.ACTIONS
where who = 'roybridge' and Whe_ >= '01/03/2011'
``````

Currently, the result = 367.069999999999

How can I modify the above query so that the result is shown in hours and minutes?

Thanks
0
Chris Millard
3 Solutions

Commented:
Try this:

``````select
select CAST(CAST(SUM(timetaken) / 24 as int) as nvarchar(10)) + 'h '
+ CAST(CAST(SUM(timetaken) % 24 as int) as nvarchar(10)) + 'm' as SumTimeTaken
from dbo.ACTIONS
where who = 'roybridge' and Whe_ >= '01/03/2011'
``````
0

Commented:
Sorry, it included an extra 'select', which is removed below:

``````select CAST(CAST(SUM(timetaken) / 24 as int) as nvarchar(10)) + 'h '
+ CAST(CAST(SUM(timetaken) % 24 as int) as nvarchar(10)) + 'm' as SumTimeTaken
from dbo.ACTIONS
where who = 'roybridge' and Whe_ >= '01/03/2011'
``````
0

Data and Analytics ManagerCommented:
What's the value currently - is the whole part hours, and the decimal part fractional hours? Or is the whole part minutes, and you just want to drop the fractional part? Also, what do you want the output to look like - "HH:MM", or "X hours, Y minutes"?

Assuming your number is whole minutes and you want to drop the fraction, you could do something like this:

``````DECLARE @number float
SET @number = 367.069999999999

SELECT CONVERT(VARCHAR, CONVERT(INT, @Number/60)) + ' hours, ' + CONVERT(VARCHAR, CONVERT(INT, @Number % 60)) + ' minutes'
``````

If you want something different, please post some more details.
0

Author Commented:
@wdosanjos - when I execute the SQL query, I get the following eror:-

``````Msg 402, Level 16, State 1, Line 1
The data types float and int are incompatible in the modulo operator.
``````
0

Commented:

``````select CAST(FLOOR(SUM(timetaken) / 60) as nvarchar(10)) + 'h '
+ CAST(FLOOR((SUM(timetaken) / 60 - FLOOR(SUM(timetaken) / 60)) * 60) as nvarchar(10)) + 'm' as SumTimeTaken
from dbo.ACTIONS
where who = 'roybridge' and Whe_ >= '01/03/2011'
``````
0

Data EngineerCommented:
This will convert the duration into HH:MI:SS format. try this.
``````DECLARE  @dur INT

SELECT @dur = SUM(timetaken)
FROM dbo.ACTIONS
WHERE who = 'roybridge'
AND Whe_ >= '01/03/2011'

SELECT CASE
WHEN @dur / 3600 < 10 THEN '0' + CONVERT(VARCHAR(10),@dur / 3600)
ELSE CONVERT(VARCHAR(10),@dur / 3600)
END + ':' + CASE
WHEN (@dur%3600) / 60 < 10 THEN '0' + CONVERT(VARCHAR(10),(@dur%3600) / 60)
ELSE CONVERT(VARCHAR(10),(@dur%3600) / 60)
END + ':' + CASE
WHEN @dur%60 < 10 THEN '0' + CONVERT(VARCHAR(10),@dur%60)
ELSE CONVERT(VARCHAR(10),@dur%60)
END
``````
0

Author Commented:
I'm spliting the points between everyone. Basically, my query was wrong. The query was using the format mm/dd/yyyy whereas I was expecting dd/mm/yyyy. When I modify the query, I get the results I was expecting.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.