Post reply

Formating time over 1 day

  • October 6, 2009 at 2:57 pm

    #226864

    I have a quick quesiton

    I have a field that comes back in seconds. The users want to see this in a HH:MM:SS format. easy enough, except now they want a total of the whole report....which is greater than 1 day.

    I would really like a format of HHH:MM:SS, but I would settle for a DD:HH:MM:SS.

    The problem with the built in function is that it converts it to a datetime ( which means that pouplates the day to 1 ) even if there is less than one day of time in seconds.

    Anyone know of a standard date format that could help?

    Eric

  • October 6, 2009 at 4:11 pm

    #1062530

    select

    [HHHHH:MM:SS] =

    right('00000'+convert(varchar(20),Seconds/3600),5)+

    right(convert(varchar(20),dateadd(ss,Seconds%3600,0),108),6)

    from

    (

    -- Test Data

    select Seconds =(14*86400)+(13*3600)+(59*60)+14

    ) a

    Results:

    HHHHH:MM:SS

    -----------

    00349:59:14

    (1 row(s) affected)

Viewing 2 posts - 1 through 1 (of 1 total)

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