Convert minutes to Hours:Minutes
-
Can someone please show me how to write the Select statement where i can Convert a value that is in minutes...what I mean by that is the integer reads like 187...it needs to be converted so that it comes out to 3:07.
The math would be minutes/60 = hours:minutes (i think)
I hope that this is an easy task.
Thank you!
-
If it was never going to be more than a 24 hours worth you might try this.
SELECT LEFT(Convert(char,DATEADD(n,187,0),114),5)
However if it may ever exceed 24 hours worth you will have a wrong value. Or if you just don't want a leading zero the above will not work.
Instead use
SELECT CAST((187/60) as varchar(10)) + ':' + RIGHT('0' + CAST((187-(60*(187/60))) as varchar(2)),2)
OR
SELECT CAST((187/60) as varchar(10)) + SUBSTRING(Convert(char,DATEADD(n,(187-(60*(187/60))),0),114),3,3)
-
Thank you for your quick response!
I tried your suggestion, but I was not successfull...I believe that it has to do with my columns and the aggregate function.
Here is the code that I am playing with:
SELECT Count(Col011), Col011, Col010, Sum(CAST(([Col014]/60) as varchar(10)) + SUBSTRING(Convert(char,DATEADD(n,([Col014]-(60*([Col014]/60))),0),114),3,3)) FROM S526960.seg_all2 WHERE Col010 = '"+date+"' AND (Col011 = 'UTOP' OR Col011 = 'LOA' OR Col011 = 'BEREAV' OR Col011 = 'JURY') GROUP BY Col011, Col010
When I run this, I get the following error:
The sum or average aggregate operation cannot take a varchar data type as an argument.
This does not make sense because Col014 is an int datatype.
Do you have any suggestions?
-
declare @hr varchar(2),
@min varchar(2),
@in int,
@HrMin int
set @in = 187
set @hr = @in/60
set @HrMin = @hr*60
set @min = @in - @HrMin
if len(@min) = 1
set @min = '0'+convert(varchar(2),@min)
print 'Time = '+@hr+':'+@min
As for the aggregate error look at the data type on Col011. Count is also a aggregate function.
Good Luck
-
I apologize...I do not understand what you wrote????
Is this supposed to work in the SQL Server?
-
Maybe I need to clarify a little...
I have a column in my SQL Server tbale that I need to count records that have specific criteria, I also need to sum up a column by the same criteria.
This is what sent in a previous thread. My issue is that the column that needs to be summed up is currently showing in a whole number that represents minutes.
I need to sum up those minutes but show them as hours and minutes.
Please advise what I will need to do to accomplish this.
Thank you very much for your assistance!
-
select dateadd(mm,yoursum,getdate() - getdate()) as yourTime_1900_01_01
check out dateadd in BOL
this example may help out :
-- SQLServer uptime
SELECT @@servername as ServerName, Year( SQLServer_UpTime) - 1900 - case when month( SQLServer_UpTime) - 1 - case when day( SQLServer_UpTime) - 1 < 0 then 1 else 0 end < 0 then 1 else 0 end as Years
, month( SQLServer_UpTime) - 1 - case when day( SQLServer_UpTime) - 1 < 0 then 1 else 0 end as Months
, day( SQLServer_UpTime) - 1 as Days
, substring(convert(varchar(25), SQLServer_UpTime,121),12,8) as Timepart
from (
SELECT getdate() - login_time as SQLServer_UpTime -- opgepast start vanaf 1900-01-01
FROM master..sysprocesses
WHERE spid = 1
) a
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
I generally use a function like this called with milliseconds as such:
Print Common.dbo.mSecsToHHMMSSmmm(DateDiff(ms,@StartTime,@EndTime))
Use Common
Go
If Object_Id('mSecsToHHMMSSmmm') is not Null Drop Function mSecsToHHMMSSmmm
Go
--------------------------------------------------------------------------------
-- Name:
-- mSecsToHHMMSSmmm
--
-- Description:
-- Converts the specified number of milli-seconds to HH:MM:SS.mmm string format.
-- The HH portion of the result may be longer than two characters.
--
-- Parameters:
-- @ms Number of milli-seconds to convert to HH:MM:SS.mmm format.
--
-- History:
-- 2004-03-26 PeteK - Created this Function.
--
--------------------------------------------------------------------------------
Create Function mSecsToHHMMSSmmm
(
@ms BigInt
)
Returns VarChar(20)
As Begin
Declare @h Int,
@m Int,
@s Int,
@Str VarChar(256)
If @S<0 Begin
Set @STR='??:??:??.???'
Goto AllDone
End
Set @h=@ms/3600000
Set @ms=@ms-(@h*3600000)
Set @m=@ms/60000
Set @ms=@ms-(@m*60000)
Set @S=@ms/1000
Set @ms=@ms-(@s*1000)
Set @STR=Cast(@h as VarChar(10))
If Len(@Str)<2 Set @STR=Right('00'+@Str,2)
Set @STR=@Str+':'+Right('0'+Cast(@m as VarChar(2)),2)+':'+
Right('0'+Cast(@s as VarChar(2)),2)+'.'+
Right('00'+Cast(@ms as VarChar(3)),3)
AllDone:
Return @STR
End
PeteK
I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be. -
OK, simple solution:
declare @mins int
select @mins = 187
select convert(varchar, floor(@mins/60))+':'+right('0'+convert(varchar, @mins % 60),2)
The above uses a variable to store the minutes, but if you have a table called SomeTable with an integer column in it called Minutes, you'd want to do this:
select convert(varchar, floor(Minutes/60))+':'+right('0'+convert(varchar, Minutes % 60),2) from SomeTable
Just in case you need to know and it's not obvious to you (apologies if this seems condascending, but I don't know you):
convert(varchar, floor(Minutes/60)) - This just gives you the hours as a varchar by dividing the minutes by 60 and throwing away the decimal portion (unless minutes is negative...)
convert(varchar, Minutes % 60) - This gives you the number of minutes left over by using the modulo operator %
right('0'+<minutes bit>,2) - This around the above just ensures that you have a leading zero by concatenating one on the front regardless, and then taking the rightmost two digits.
I hope this gives you exactly what you asked for - a way to select a string from a table where an integer is stored.
Steve
Steve -
This is only to clarify the error message (the answer to your question was perfectly addresed by stephenkendrick ...)
In your query you use:
Sum(CAST(([Col014]/60) as varchar(10))
and this is the source of error: CAST ... as varchar cannot be input to a SUM().
HTH
Gigi
-
As a general suggestion since several viable solution approaches have been described, you can get yourself into an indecipherable mess if you try to do the conversion and the aggregation in one statement. I would make a view that would do the conversion and return the remainder of the rows. You will need to include the minutes column since you won't be able to do any math on a field that's hh:mm. Once you get this view working, your grouping should be a drop kick.
I'd also second the suggestion to use a function to do the conversion, it will save a lot of work down the road as you'll use it a lot more than you might think.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font] -
Thank you all for your valuable responses.
I have it working now...thanks to all of you.
-
thanks stephenkendrick, your example worked perfectly for me. Time arithmatic sucks!
-
As suggested a couple of times... First SUM the minutes and THEN convert the result for display using one of the many methods shown.... you cannot aggregate non-numeric data with SUM.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Heh... like anything else, it only sucks when you don't know how
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply