Post reply

How to get exact figures in fraction as in execl in sql server.

  • July 17, 2013 at 11:54 pm

    #280541

    Hi,

    In Excel 29/30 gives me 0.966666667 that is 0.97

    But In Sql it gives as 0.

    I had tried the following code,but it gives as 0.

    Select Convert(Numeric(12,2),29/30)

    Please suggest me how to get answer as exact fraction in sql that is 0.97.

    Thanks in Advance!

  • July 18, 2013 at 12:13 am

    #1633527

    Something like this should work

    DECLARE@num1 NUMERIC(12,2)

    DECLARE@num2 NUMERIC(12,2)

    SELECT@num1 = 29, @num2 = 30

    SELECT CONVERT(NUMERIC(12,2),@num1/@num2)

    OR this

    SELECT 29/30.00

    This issue happened because when you do something like SELECT CONVERT( NUMERIC(12,2), 29/30 ), an INTEGER division is done between 29 and 30 which gives the result as 0

    0 is then converted to NUMERIC(12,2) which is again 0

    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • July 18, 2013 at 12:16 am

    #1633529

    Thanks Kingston !!

    Thanks for very well explanation.......!

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

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