Post reply

how to convert seconds to HH:MM:SS format?

  • February 2, 2009 at 11:12 am

    #77249

    how to convert seconds to HH:MM:SS format?

  • February 2, 2009 at 11:22 am

    #936610

    Let me know if this fits your bill:

    declare @secs tinyint,

    @mins tinyint,

    @hrs smallint,

    @seconds int;

    set @seconds = 1234567;

    set @secs = @seconds % 60;

    set @mins = (@seconds / 60) % 60;

    set @hrs = (@seconds / 60) / 60;

    select

    @seconds,

    @hrs,

    @mins,

    @secs,

    cast(@hrs as varchar(8)) + ':' + right('0' + cast(@mins as varchar(2)), 2) + ':' + right('0' + cast(@secs as varchar(2)), 2);

  • February 2, 2009 at 11:26 am

    #936616

    it's kind of easy...

    in your case, you only care about the time portion of any date....not the date itself.

    so you ADD the number of seconds to an arbitrary date,

    then one of the CONVERT functions paramneters allowed you to get just the TIME portion of a specified date:

    SELECT DATEADD(month, 147859, '2006-08-31') --adding a bunch of seconds to any ol date

    Results:2006-09-01 17:04:19.000

    SELECT CONVERT(VARCHAR,DATEADD(second, 147859, '2006-08-31') ,114) --selecting that same date, and formatting it to HH::MM:SS:nnnn

    Results:17:04:19:000

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • February 2, 2009 at 12:37 pm

    #936662

    CONVERT(VARCHAR(8), DATEADD(ss, 12345, 0) ,114) will cut off milliseconds.

    _____________
    Code for TallyGenerator

Viewing 4 posts - 1 through 3 (of 3 total)

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