Post reply

Division problem in SQL

  • June 7, 2013 at 3:38 am

    #276036

    Hello All,

    Can anyone please explain why do we always get 0 when we divide 1 by any other larger number.

    select 1/2 gives me 0.

    Thank you for your help

  • June 7, 2013 at 3:55 am

    #1622012

    You are default to int. Int is an integer (whole number). This means that it will remove any decimal places from the value.

    e.g. 6/4 will return 1 instead of 1.5

    To fix this you need to declare a decimal type (if in a procedure) or convert to float or decimal:

    select 1/cast(2 as float);

    select convert(decimal(8,2), (1))/5;

    Dird

  • June 7, 2013 at 4:08 am

    #1622018

    Thank you for your response.

  • June 7, 2013 at 4:40 am

    #1622022

    The link below has the answer to your question

    http://msdn.microsoft.com/en-us/library/ms175009.aspx

    If an integer dividend is divided by an integer divisor, the result is an integer that has any fractional part of the result truncated.

    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/

  • June 11, 2013 at 6:33 am

    #1622855

    Dird (6/7/2013)

    To fix this you need to declare a decimal type (if in a procedure) or convert to float or decimal:

    select 1/cast(2 as float);

    select convert(decimal(8,2), (1))/5;

    A simple decimal point will do the trick.

    SELECT 1./2

    The SQL Guy @ blogspot[/url]

    @SeanPearceSQL

    About Me[/url]

  • June 11, 2013 at 6:46 am

    #1622860

    Sean Pearce (6/11/2013)

    A simple decimal point will do the trick.

    SELECT 1./2

    Perhaps but I was assuming he wants this for a stored procedure or something passing parameter values~...but then I guess they would be of type decimal anyways~

    Dird

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

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