How to find values like 9.0949470E-13 and convert to zero? 2

[steve728]

For some reason some of the balances in my table have values like 9.0949470E-13 (exponential values?). They're wreaking havoc. Please give me a sample of an update SQL statement that will change them to zero.

Thanks,

Steve

 

[gmmastros]

Update Table
Set Field = 0
Where Field < 0.0001

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[RoyVidar]

The field(s) in question is probably single or double? If so, I think what yo see is the result of errors in floating point arithmetic. For economical stuff, using the Currency datatype can give better precision. Could that be something to consider?

Some "geeky" reading

http://support.microsoft.com/default.aspx?kbid=42980

Roy-Vidar

 

[steve728]

You both get stars! gmmastros gave me the perfect code to
find and fix the problem. And you explained the possible source and solution to avoid the issue in the future.

You always come through!

Steve