Results 1 to 13 of 13
  1. #1
    Join Date
    Jul 2007
    Posts
    7

    Unanswered: Integer / Integer

    Hi All,

    If I run "SELECT 610/100", query analyser returns 6! (wrong)

    If I run "SELECT 610.0/100", query analyser returns 6.100000! (correct)

    I know I can get the correct result by running "SELECT Cast(610.0 as Float)/100", but why doesn't the first example return 6.1?

    Is there some setting on my server which says Integer / Integer = Integer ?

    Regards

    Xo

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    That's integer division. Not a server setting. BTW - I would be cautious casting to float, depending on what you are doing of course.

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Computers are stupid - remember that
    You have to be explicit and watch out when using approximate data types (float).

    Using ".0" implicitly converts it to a decimal; hance a decimal result.
    George
    Home | Blog

  4. #4
    Join Date
    Sep 2005
    Posts
    161
    Another approach:

    select (col1*1.0)/(col2*1.0)

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by cascred
    Another approach:

    select (col1*1.0)/(col2*1.0)
    You only need one input to be a decimal to get decimal returned, so select 1.0*col1/col2 works fine too.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The original poster should probably read about Transact-SQL expressions, especially the part about type conversion.

    -PatP

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    And why bother to even have a datatype of float if it gives you the wrong answer?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Because it doesn't. The problem is 99% of the time people use float they are asking the wrong question.

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by pootle flump
    Because it doesn't. The problem is 99% of the time people use float they are asking the wrong question.

    For example?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Heh. We've been here before.
    http://www.dbforums.com/showthread.php?t=1619606
    Quote Originally Posted by Brett Kaiser
    For example?
    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=71391 (Check out MVJ's posts in this thread - they are probably the most illuminating few lines on the topic I have read anywhere about floats - and it includes two examples ).

  11. #11
    Join Date
    Sep 2005
    Posts
    161
    Quote Originally Posted by Brett Kaiser
    And why bother to even have a datatype of float if it gives you the wrong answer?
    You need float if you are going to represent very large or very small numbers. What else can you use to represent 10E+100?

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Great stuff
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    One of the first things that ought to be in every computer language class is that INT and its kin are for things you count, FLOAT and its kin are for things that you measure.

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •