Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2002
    Location
    Washington D.C.
    Posts
    164

    Unanswered: Tax rate data type

    Hi,
    I want to store tax rate in my tables. I set the data type to float, I wan't 4 decimal places and the data in the table has 4 decimals, but when I run a query in query analyzer it returns: 4.4999999999999998E-2 instead of 0.045.
    How can I fix this?
    The intellect is better than desire, for the intellect makes you a king over your destiny, and desire makes you a slave of your destiny.

  2. #2
    Join Date
    Jul 2004
    Location
    New Zealand
    Posts
    9
    float data types usually have some degree of inaccuracy. from the TSQL Help:

    Floating point data is approximate; not all values in the data type range can be precisely represented.

    When playing on my computer, I found that the only number that return accurately from a select were whole numbers. Given 1 decimal place and the inaccuracy begins.

    I also found that if i assigned the value from the database to a local variable, it would then print accurately.

    declare @tax float
    select * from james_table where row2 < 15
    select @tax = tax_rate from james_table where row2 = 14

    print @tax

    Gave the output:

    row1 row2 row3 tax_rate
    ------ ---- ----------- -----------------------------------------------------
    11 11 11 4.4999999999999998E-2
    12 12 12 4.3999999999999997E-2
    13 13 13 4.2999999999999997E-2
    14 14 14 4.3209999999999998E-2
    10 10 10 4.0
    9 9 9 1.1000000000000001

    (6 row(s) affected)

    0.04321

  3. #3
    Join Date
    Dec 2002
    Location
    Washington D.C.
    Posts
    164
    Thanks for your reply.
    The work around that I have for know is to select the tax in the following format:

    SELECT LEFT(Tax,7) and it will return the correct value.

    This is the first time that I'm using percents with SQL server, and most Microsoft sample databases that I've looked at use float for tax rate.

    Do you have experience with tax rates and sql server data types?
    The intellect is better than desire, for the intellect makes you a king over your destiny, and desire makes you a slave of your destiny.

  4. #4
    Join Date
    Jul 2004
    Location
    New Zealand
    Posts
    9
    No, I had some previous knowledge of how floats are typically stored on PCs.

    A lot of applications I have come across use int data types for storing price and cost information, which is often calculated to 3 or 4 decimal places. They use a data dictionary to recall how many decimal places a field should have. And work from there. It doesn't seem like a particularly good solution to me. But it may be slightly quicker than using left?

    Of course, I don't think that there is anything to worry about here. floats just cannot precisely represent results. its because they use exponents, so they are always stored as a result to the power of something. But this doesn't mean that they will drastically effect the accuracy your calculations. So the biggest problem would be displaying the results of a query accurately and clearly.

    In which case something like what you are doing should be fine. Interesting... it doesn't matter what value you insert as the length arg:

    SELECT row2, left(tax_rate, 20)
    FROM James_Table

    row2
    ---- --------------------
    18 NULL
    11 0.045
    18 NULL
    18 NULL
    18 NULL
    12 0.044
    13 0.043
    14 0.04321
    10 4
    9 1.1
    8 4.5e-006

    (11 row(s) affected)

  5. #5
    Join Date
    Dec 2002
    Location
    Washington D.C.
    Posts
    164
    Thanks for your reply,
    Now I undestand it better (the fact that its a presentation issue).
    The intellect is better than desire, for the intellect makes you a king over your destiny, and desire makes you a slave of your destiny.

Posting Permissions

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