Calculate Hours and Minutes - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Calculate Hours and Minutes

Author

Topic

chipembele
Posting Yak Master

106 Posts

Posted - 2006-11-06 : 10:43:36

Hi
Can anyone help me convert a number to give the result in hours and minutes? For example 195 as 3:15 or 210 as 3:30. We are trying to create a report showing hours and minutes worked without having to export to Excel.

I've had a look around the net and this seems to be quite a difficult function in SQL Server.

Any guidance much appreciated.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-11-06 : 11:00:08

[code]
declare @mins int

select @mins = 195

select convert(varchar(10), @mins / 60) + ':' + convert(varchar(10), @mins %60)
[/code]

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-11-06 : 11:14:11

[code]
select
ElapsedTime =
convert(varchar(5),dateadd(minute,Min,0),108)
from
(select Min = 195 union all select Min = 210) a
[/code]
Results:
[code]
ElapsedTime
-----------
03:15
03:30

(2 row(s) affected)
[/code]

CODO ERGO SUM

chipembele
Posting Yak Master

106 Posts

Posted - 2006-11-07 : 03:24:08

Thanks for your replies.

Can this be built into a view (as an extra column) so it converts every row and returns the hours and minutes when you may not necessarily know the number of minutes until after the query is run?

What i'm trying to do is calculate hours worked by staff members. The query is run and sums the minutes by staff member but as I say it needs to be converting to hh:mm to make it easily readable for lecturers.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-07 : 03:25:07

Yes. Use the answer by MVJ.

Peter Larsson
Helsingborg, Sweden

chipembele
Posting Yak Master

106 Posts

Posted - 2006-11-07 : 04:38:57