<< Click to Display Table of Contents >> SQL ROUND |
The SQL keyword ROUND is not available in the Borland BDE Local SQL implementation of SQL and is only inconsistently implemented across various database systems - some times as true mathematical rounding and some times as "Bankers Rounding".
A substitute of this function is achieved by using the CAST function:
CAST(CAST(((NumericField) * 100 + 0.5) AS INTEGER) AS FLOAT) / 100
which will round to two digits by multiplying and dividing by 100 as shown.
Likewise rounding to three digits will provide multiplying and dividing by 1000:
CAST(CAST(((NumericField) * 1000 + 0.5) AS INTEGER) AS FLOAT) / 1000
The addition of 0.5 ensures proper rounding up or down by half of the intended last significant digit.
The interim use of CAST(number AS INTEGER) truncates the number into the integer part disregarding the decimals - this is why 0.5 is added before truncation to ensure e.g. 1.5 is rounded to 2 (1.5 + 0.5 truncates to 2) whereas 1.4 is rounded to 1 (1.4 + 0.5 truncates to 1).
Supplementary notes on LocalSQL added by Niels Knabe