Problem with ROUND() function

[sturgis]

Might try this, backup your data first. And as mark said, if the amount field is text you'll probably need to use Cast to make stuff work.

Code: Select all

   put "UPDATE prlabordist  SET amount =  (SELECT ROUND(amount,2))" into tSql

[Slim]

If your datatype is FLOAT, you would be best advised to NOT use the round functions as provided by the database engine (SQLite, MSSql, etc.), as there are inherent problems in doing so. For example, see this note http://www.sqlite.org/faq.html#q16 regarding SQLite, and this one http://msdn.microsoft.com/en-us/library/ms173773.aspx on the MSDN.

If that is all TL;DR,

The SQL ROUND function does not reliably work on numbers of type FLOAT. If you must stick with FLOAT, try using the CAST function and changing the datatype to DECIMAL.
For example,

Code: Select all

put "UPDATE prlabordist  SET amount =  (SELECT ROUND(CAST amount AS DECIMAL (7,2),2))" into tSql

Where

Code: Select all

AS DECIMAL (7,2)

is the total length of the data (7), and then the number of decimal places (2). This would allow for a number as high as 99999.99 .

I've recently tested this in most major DB's, and all have failed to produce satisfactory results without first converting the float to a decimal.

[Mark]

Slim,

The decimal data type would be a very good alternative for the float data type indeed. However, the rounding problem that is described on the websites that you have posted links to are no different from the rounding problems that occur in other database applications and programming languages. One may observe the same problem while doing calculations on decimal values.

Kind regards,

Mark