Post reply

Time Conversion

  • August 18, 2003 at 10:11 am

    #84121

    Hi,

    Can someone tell me the best way to convert a duration in seconds (stored as an int) to a format of hh:mm for display?

    Thanks,

    Steve

  • August 18, 2003 at 11:22 am

    #470298 

    

    declare @seconds int

    --

    set @seconds = 124

    --

    select convert(varchar(2), floor(@seconds/60)) + ':' + right('0' + convert(varchar(2), (@seconds%60)), 2)

  • August 18, 2003 at 2:32 pm

    #470331

    Thanks for the response, but that doesn't exactly work. The format I want is hh:mm, not mm:ss.

    I modified the query to look like:

    select convert(varchar(2), floor(@seconds/3600)) + ':' +

    right('0' + convert(varchar(2), (@seconds%60)), 2)

    Which works OK for some values like 3602 seconds which converts to 1:02, but not for other values like 5400

    which converts to 1:00, but should convert to 1:30.

    Any help would be greatly appreciated.

    Edited by - sdaskam on 08/18/2003 2:33:02 PM

    Edited by - sdaskam on 08/18/2003 2:34:04 PM

  • August 18, 2003 at 2:57 pm

    #470332

    so sorry, I misread it as mm:ss.

    --

    Besides, last I checked, 3602 seconds is not 1 hour and 2 minutes, but 1 hour and 2 seconds. You may need to re-check your statements...how bout:

    

    declare @seconds int

    declare @hours int

    declare @minutes int

    --

    set @seconds = 3602

    set @hours = floor(@seconds/(60*60))

    set @minutes = floor((@seconds - (@hours*3600))/60)

    --

    select right('0' + convert(varchar(2), @hours), 2) + ':' + right('0' + convert(varchar(2), @minutes), 2)

  • August 18, 2003 at 3:05 pm

    #470334

    Another option is to let SQL's built-in functions do it for you....

    declare @secs int

    select @secs = 7455

    select convert(varchar(5), dateadd(second, @secs, '00:00'), 108)

    Cheers,

    - Mark


    Cheers,
    - Mark

  • August 18, 2003 at 4:06 pm

    #470339

    Much thanks for the help guys!! I really appreciate it. Sorry about the miscalculation on my part.

    Steve

  • August 19, 2003 at 8:36 am

    #470430

    Once again, I should have just looked in BOL, huh? Good one, Mark; I didn't realize convert had a HH:MM setting.

  • August 19, 2003 at 2:57 pm

    #470478

    I wish I could claim it as my own idea. Just something I once pocketed from someone else's code.

    Cheers,

    - Mark


    Cheers,
    - Mark

Viewing 8 posts - 1 through 7 (of 7 total)

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