1

I'm importing a text file containing a field with numbers up to the thousandth decimal as a number field. I'm then running and update query to fix the SSNs (preceding zeros) and subtract 500 from the number field.

UPDATE CODImportFile 
SET CODImportFile.[Original SSN] = Format([Original SSN],"000000000")
,   CODImportFile.[Lifetime Eligibility Used] = [Lifetime Eligibility Used]-500;

I have no clue why, but some of the results look like this:

Original:500.016
Result: 1.60000000000196E-02

Any idea what could be causing this?

Andomar
  • 232,371
  • 49
  • 380
  • 404
Andy Levesque
  • 560
  • 5
  • 21
  • Firstly format SSNs `UPDATE CODImportFile SET CODImportFile.[Original SSN] = Format([Original SSN],"000000000");` then subtract `UPDATE CODImportFile SET CODImportFile.[Lifetime Eligibility Used] = [Lifetime Eligibility Used]-500;` – Sami Oct 11 '12 at 15:09
  • @Sami I get correct results when running this as a display query, but still has the same problem when running as an update query. – Andy Levesque Oct 11 '12 at 15:19
  • What is the data type of that `[Lifetime Eligibility Used]` field? – HansUp Oct 11 '12 at 15:32
  • @HansUp I have it as a double – Andy Levesque Oct 11 '12 at 16:00

3 Answers3

2

Since the data type of [Lifetime Eligibility Used] is double, the calculation in your query is similar to this:

SELECT
    CDbl(500.016) - 500 AS difference,
    TypeName(CDbl(500.016)-500) AS data_type;

... which returns this:

difference           data_type
1.60000000000196E-02 Double

Those unexpected decimal places are an artifact of the imprecision inherent with decimal math on a binary system. You could convert the field type to single (or cast double to single in the calculation), but then you would still get "extra" decimal places ... just a different set of values for those decimal places.

I think you should discard any decimal places you don't want to store.

UPDATE CODImportFile
SET [Lifetime Eligibility Used] =
    Round([Lifetime Eligibility Used]-500, 3);

Then you only need decide which rounding method you want. 

? Round(0.1245, 3)
 0.124 

? CDbl(Format(0.1245, "0.000"))
 0.125
HansUp
  • 95,961
  • 11
  • 77
  • 135
  • Answered my question perfectly. Bonus points for mentioning the rounding! I wasn't aware of the difference. – Andy Levesque Oct 11 '12 at 17:48
  • 1
    That "round to even" or "banker's rounding" aspect of `Round()` surprises many folks. It's another reason why floating point arithmetic is hard. Integer arithmetic is just so much easier. :-) – HansUp Oct 11 '12 at 17:59
0

This is probably caused by SQL treating the number as a binary, when you are expecting Decimal results: some decimal fractions can not be represented as binary without rounding error.For example, .1 cannot be represented exactly.

See Why can't decimal numbers be represented exactly in binary? for lots more info.

Community
  • 1
  • 1
Jamie F
  • 23,189
  • 5
  • 61
  • 77
0

The result there is the "correct" remainder after subtracting 500 from the original number (~0.016). Floating point numbers aren't exact anyway, which is why you have the ...00196 at the end. You should be able to fix this by specifying an appropriate scale (probably 3 in this case) in your decimal field in addition to precision.

From http://msdn.microsoft.com/en-us/library/aa258832(v=sql.80).aspx:

decimal[(p[, s])] and numeric[(p[, s])]

Fixed precision and scale numbers. When maximum precision is used, valid values are from - 10^38 +1 through 10^38 - 1. The SQL-92 synonyms for decimal are dec and dec(p, s).

p (precision)

Specifies the maximum total number of decimal digits that can be stored, both to the left and to the right of the decimal point. The precision must be a value from 1 through the maximum precision. The maximum precision is 38. The default precision is 18.

s (scale)

Specifies the maximum number of decimal digits that can be stored to the right of the decimal point. Scale must be a value from 0 through p. Scale can be specified only if precision is specified. The default scale is 0; therefore, 0 <= s <= p. Maximum storage sizes vary, based on the precision.

kitti
  • 14,663
  • 31
  • 49