Mathematical Functions

Mathematical functions return values commonly needed for operations on mathematical data. Mathematical functions have the following syntax:

FUNCTION_NAME(parameters)

For a complete list of mathematical functions, see Functions in the Microsoft SQL Server Transact-SQL Reference.

Mathematical functions operate on decimal, numeric, integer, float, real, money, or smallmoney numeric data. The precision of built-in operations on float datatype data is six decimal places by default.

By default, a number passed to a mathematical function will be interpreted as a numeric datatype. The CONVERT function can be used to "cast" the datatype to something else, such as a float. For example:

SELECT FLOOR (123.45)

---------------------
123

(1 row(s) affected)

is a numeric and by default returns 123.

But this example:

SELECT FLOOR (CONVERT (float, 123.45))

-------------------------------------
123.0

(1 row(s) affected)

is a float and returns 123.0.

And

SELECT STR (FLOOR (123.45), 8, 3)

---------------------------------
123.000

(1 row(s) affected)

is a numeric nested within STR function, so the result as char with explicit "8,3" formatting (123.000).

Error traps are provided to handle domain or range errors of mathematical functions. A user can set the options ARITHABORT or ARITHIGNORE, which respectively terminate the query or return NULL when a mathematical function encounters a domain error condition. No warning message is displayed. If neither of these options is set, the system returns NULL and prints a warning message after the query is executed. (For additional information about the ARITHABORT and ARITHIGNORE options, see the SET statement in the Microsoft SQL Server Transact-SQL Reference.)

The ROUND function always returns a value even if the length is illegal. If the specified length is positive and longer than the digits after the decimal point, 0 is added after the fraction digits.

If the length is negative and larger than or equal to the digits before the decimal point, ROUND returns 0.00.

For details about the mathematical functions, see Functions in the Microsoft SQL Server Transact-SQL Reference.