Post reply

Converting a smallint to time

  • August 26, 2007 at 5:50 pm

    #121263

    I have a column in a table that has a datatype of smallint which stores the number of seconds that transpires for a phone call. I would like to convert the data to a time value.

    For instance, if the value is 133 (meaning the call took 133 seconds), I would like to show it as 02:13. I have searched all forums on this site, googled it, and searched books on line with no help.

    Any ideas?

    Thanks,

    Brian

  • August 26, 2007 at 7:13 pm

    #729463

    Try this

    declare @sec smallint

    set @sec = 133

    select convert(varchar(8), dateadd(second,@sec,0), 114) as WithHour

    select right(convert(varchar(8), dateadd(second,@sec,0), 114), 5) as WithoutHour

    --------------------
    Colt 45 - the original point and click interface

  • August 27, 2007 at 2:55 am

    #729493

    Keep it simple. Why use 114 (with milliseconds when source data is not?

    SELECT CONVERT(VARCHAR, @Sec, 108)

     

    N 56°04'39.16"
    E 12°55'05.25"

  • August 27, 2007 at 4:19 pm

    #729666

    Ah yes forgot about style 108, thanks Peter.

    --------------------
    Colt 45 - the original point and click interface

  • August 27, 2007 at 5:37 pm

    #729673

    Thanks for your quick replies. I tried both. Phil's gave me the results I was looking for, but but Peter's did not. Not sure why.

    Thanks again!

  • August 27, 2007 at 6:00 pm

    #729674

    Peter's response was just on my time conversion. So in my solution you can use style 108 instead of 114.

    eg:

    select convert(varchar(8), dateadd(second,@sec,0), 108) as WithHour

    select right(convert(varchar(8), dateadd(second,@sec,0), 108), 5) as WithoutHour

    --------------------
    Colt 45 - the original point and click interface

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

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