SQL ROUND

<< Click to Display Table of Contents >>

Navigation:  Notes on Local SQL >

SQL ROUND

Previous pageReturn to chapter overviewNext page
hmtoggle_plus1See also

ROUND(real number)

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