Post reply

Turn integer into time of day

  • August 16, 2011 at 3:55 pm

    #246365

    I am sad to say I haven't been able to figure this out. I have a table with a Start Time and an End Time, but it listed as an integer supposedly minutes. So if the Table lists

    960 it should be 4:00 pm, if it lists 510 is should be 8:30 am, if it lists 945 it should be 3:45 pm, etc..,.

    I thought this would be simple (and sadly probably is) but I am stumped!

  • August 16, 2011 at 4:06 pm

    #1369354

    use master

    declare @Foo as table(a int)

    insert into @Foo

    select 960

    union

    select 510

    union

    select 945

    Here is code that I got from this site a few days ago.

    select convert(varchar, floor((convert(numeric(9,0), a))/60)) + ':' + right('0' + convert(varchar, (convert(numeric(9,0), a)) % 60),2)

    from @Foo

    Living in Paradise: N 34°16'07.99" W 119°12'17.58"

  • August 16, 2011 at 4:07 pm

    #1369355

    something like this?

    SELECT DATEADD(mi,yourColumn,'19000101')


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • August 16, 2011 at 4:21 pm

    #1369359

    @ SSC-Enthusiastic

    Perfect! You Rock!

  • August 16, 2011 at 4:28 pm

    #1369362

    @tim-2:

    Varchar without any length specification woud use a default of 30, which is oversize for the given task. Depending on the requirement how to display more than 24hrs (or 100hrs and up to be precise) a VARCHAR(2) should be used.

    When looking at the code it performs 9 operations (conversion/functions/math operation) compared to one (or two/three, if a conversion is required).

    DATEADD() might be the better alternative.


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • August 16, 2011 at 5:11 pm

    #1369376

    baileyc411 (8/16/2011)

    I am sad to say I haven't been able to figure this out. I have a table with a Start Time and an End Time, but it listed as an integer supposedly minutes. So if the Table lists

    960 it should be 4:00 pm, if it lists 510 is should be 8:30 am, if it lists 945 it should be 3:45 pm, etc..,.

    I thought this would be simple (and sadly probably is) but I am stumped!

    What do you want to happen when the start time is 1380 minutes and the end time is 60 minutes the next day? How about when you eventually want to calculate how much the elapsed time is? And don't say they won't... it ALWAYS comes to that someday. 😉

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

  • August 17, 2011 at 11:23 am

    #1369757

    So currently the schedule programe they use won't allow that. (i.e. You would have to enter 7:00-12:00 for one day and 12:00 to 2:00 for next). I am seeing that this is 24 hours time as opposed to 12 hour so now I need to figure how to fix that.

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

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