Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2006
    Posts
    111

    Unanswered: [RESOLVED] Can anyone help with with datatype float

    I have some engineering data in my table and the db designer is representing it with a float datatype. Here's what is happening. If I query on a record based on id num and get a row and put it in text boxes in my Windows App, min_riv_hd_dia (the float) is 0.026<14 zeroes>2. If I try to query and get that same record again but this time based on id num and min_riv_hd_dia equal to 0.026<14 zeroes>2, I get no row found. If I just do a select on this row based on id number, sql server displays it as 0.026. But if I query with 0.026 as my value, still the row is not found. If I query min_riv_hd_dia > 0.026, the record is found.

    So my question is, how can I tell the exact value that must be input in my search criteria in order to find this row?

    Thank you so much if you can help!
    Last edited by M Mock; 08-30-07 at 18:08. Reason: resolved

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    well, you have discovered that float is not a percise data type and is only appropriate for some scientific and engineering calculations. Switch to decimal with the correct percision or scale or do not search on the float field.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by M Mock
    So my question is, how can I tell the exact value that must be input in my search criteria in order to find this row?
    That's the crux of it - it is not an exact value.

    A couple of interesting links:
    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=71391
    http://docs.sun.com/source/806-3568/ncg_goldberg.html
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Jul 2006
    Posts
    111
    Thank you both.

    I like Thrasymachus' suggestion...do not search on the float field.

  5. #5
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    If you have to compare floating-point values. Check that the difference between them is small compared to the size of the numbers, rather than look for exact equality (i.e. use rounding).

    where round(min_riv_hd_dia,10)=0.026

    float is an approximate numeric datatype and should not be used where absolute precision is required. This type is useful for applications that need large numbers but do not need precise accuracy. If you require very accurate numbers, use the decimal data type (e.g. in financial applications).

    For more info see
    What Every Computer Scientist Should Know About Floating-Point Arithmetic

    Also interesting to note is that floating point arithmetic is used in excel and many combinations of arithmetic operations may produce results that appear to be incorrect by very small amounts.
    see http://support.microsoft.com/kb/214118/

Posting Permissions

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