Solved

# SQL Query - convert decimal to hours and minutes

Posted on 2011-03-22
Medium Priority
3,738 Views
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
Question by:Chris Millard
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 23

Expert Comment

ID: 35191380
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

LVL 23

Expert Comment

ID: 35191390
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

LVL 28

Assisted Solution

Ryan McCauley earned 664 total points
ID: 35191396
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

LVL 17

Author Comment

ID: 35191533
@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

LVL 23

Accepted Solution

wdosanjos earned 668 total points
ID: 35191963

``````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

LVL 41

Assisted Solution

Sharath earned 668 total points
ID: 35191991
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

LVL 17

Author Closing Comment

ID: 35199994
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

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
###### Suggested Courses
Course of the Month12 days, 9 hours left to enroll