Post reply

CONVERT :( --> :) - Strange query same result

  • March 24, 2009 at 5:06 am

    #75440

    All,

    I have executed the below queries today.

    select convert(decimal(16,2), 12344.83937898)

    select convert(decimal(16,2), '12344.83937898')

    select ROUND(convert(float, 12344.83937898),2)

    All of them returned the same result.

    i.e 1234.84

    I want to know what is happening in #2 as well as in #3.

    Inputs are welcome!

    karthik

  • March 24, 2009 at 5:29 am

    #965073

    1 and 2 are almost the same.

    your string can be converted to a decimal directly from

    '12344.83937898' to .12344.83937898

    Now when converting a numeric/decimal to a numeric/decimal, rounding occurs, you can see a nice table in BOL about what happens when you convert from different types.

    WIth regards to the 3 well you have used the ROUND function and specified that only 2 decimals should be allowed, so there for all the solutions will round to 2 decimal places.

    PS what results were you expecting?

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life

  • March 24, 2009 at 5:39 am

    #965077

    Thanks Chris!

    I want to know how sql memory will be allocated for those 3 statements? How sql optimizer process the above queries?

    karthik

  • March 24, 2009 at 5:40 am

    #965078

    I mean how many bits or bytes will be allocated to each statements? which one will take more space?

    karthik

  • March 24, 2009 at 5:44 am

    #965079

    ah I see,

    I'm sorry but my knowledge of memory consumption is limited but my guess is going to be that the first statement is the best.

    2 is out cause SQL has to do a varchar to numeric conversion which statement one doesn't have to do.

    And well I just assume (I know it's bad) that the implicit numeric conversion in one is better than the Float conversion in 3.

    Most people recommend not using floats as they not accurate.

    Sorry but that's not really a real answer, I look forward to others replies 🙂

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life

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

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