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-2<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-2=@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)

  • 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)

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply