Chapter 15: Using the Built-In Functions in Queries

Examples of using mathematical functions

The mathematical built-in functions operate on numeric data. Certain functions require integer data and others approximate numeric data. A number of functions operate on exact numeric, approximate numeric, money, and float types. The precision of built-in operations on float type data is six decimal places by default.

Error traps are provided to handle domain or range errors of the mathematical functions. Users can set the arithabort and arithignore options to determine how domain errors are handled. For more information about these options, see “Conversion errors”.

Table 15-9 displays some simple examples of mathematical functions.

**Table 15-9: Examples of mathematical functions**
Statement	Result
select floor(123) select floor(123.45) select floor(1.2345E2) select floor(-123.45) select floor(-1.2345E2) select floor($123.45)	`123 123.000000 123.000000 -124.000000 -124.000000 123.00`
select ceiling(123.45) select ceiling(-123.45) select ceiling(1.2345E2) select ceiling(-1.2345E2) select ceiling($123.45)	`124.000000 -123.000000 124.000000 -123.000000 124.00`
select round(123.4545, 2) select round(123.45, -2) select round(1.2345E2, 2) select round(1.2345E2, -2)	`123.4500 100.00 123.450000 100.000000`

The round(numeric, integer) function always returns a value. If integer is negative and exceeds the number of significant digits in numeric, Adaptive Server rounds only the most significant digit. For example, this returns a value of 100.00:

select round(55.55, -3)

The number of zeros to the right of the decimal point is equal to the scale of the first argument’s precision plus 1.

View this book as PDF