Mathematical Functions

Mathematical functions return values commonly needed for operations on mathematical data. Mathematical function names are not keywords.

Syntax

function_name(parameters)

where

function_name

Specifies a mathematical function.

These are the mathematical functions:

Function	Parameters	Result
ABS	(numeric_expr)	Absolute value of the numeric expression. Results returned are of the same type as numeric_expr.
ACOS	(float_expr)	Angle (in radians) whose cosine is the specified approximate numeric (float) expression.
ASIN	(float_expr)	Angle (in radians) whose sine is the specified approximate numeric (float) expression.
ATAN	(float_expr)	Angle (in radians) whose tangent is the specified approximate numeric (float) expression.
ATN2	(float_expr1, float_expr2)	Angle (in radians) whose tangent is (float_expr1/float_expr2) between two approximate numeric (float) expressions.
CEILING	(numeric_expr)	Smallest integer greater than or equal to the numeric expression. Result returned is the integer portion of the same type as numeric_expr.
COS	(float_expr)	Trigonometric cosine of the specified angle (in radians) in an approximate numeric (float) expression.
COT	(float_expr)	Trigonometric cotangent of the specified angle (in radians) in an approximate numeric (float) expression.
DEGREES	(numeric_expr)	Degrees converted from radians of the numeric expression. Results are of the same type as numeric_expr.
EXP	(float_expr)	Exponential value of the specified approximate numeric (float) expression.
FLOOR	(numeric_expr)	Largest integer less than or equal to the specified numeric expression. Result returned is the integer portion of the same type as numeric_expr.
LOG	(float_expr)	Natural logarithm of the specified approximate numeric (float) expression.
LOG10	(float_expr)	Base-10 logarithm of the specified approximate numeric (float) expression.
PI	( )	Constant value of 3.141592653589793.
POWER	(numeric_expr, y)	Value of numeric expression to the power of y, where y is a numeric datatype (decimal, float, int, money, numeric, real, smallint, smallmoney, or tinyint). Result is of the same type as numeric_expr.
RADIANS	(numeric_expr)	Radians converted from degrees of the numeric expression. Result is of the same type as numeric_expr.
RAND	([seed])	Random approximate numeric (float) value between 0 and 1, optionally specifying an integer expression (tinyint, smallint, or int) as the seed.
ROUND	(numeric_expr, length)	Numeric expression rounded off to the length (or precision) specified as an integer expression (tinyint, smallint, or int). Result is of the same type as numeric_expr. 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.
SIGN	(numeric_expr)	Returns the positive (+1), zero (0), or negative (-1) sign of the numeric expression. Result is of the same type as numeric_expr.
SIN	(float_expr)	Trigonometric sine of the specified angle (measured in radians) in an approximate numeric (float) expression.
SQRT	(float_expr)	Square root of the specified approximate numeric (float) expression.
TAN	(float_expr)	Trigonometric tangent of the specified angle (measured in radians) in an approximate numeric (float) expression.

where

numeric_expr

Is an expression of the decimal, float, int, money, numeric, real, smallint, smallmoney, or tinyint datatype.

float_expr

Is an expression of the float datatype.

Remarks

Internal conversion to float can cause loss of precision if the money datatype is used.

Error traps are provided to handle domain or range errors of these functions. A user can set the options ARITHABORT, which terminates the query, or ARITHIGNORE, which returns NULL, when a domain error occurs. No warning message is displayed. If neither of these options is set, SQL Server returns NULL and returns a warning message after the query is executed.

Examples

A.    FLOOR Function

This example shows positive numeric, negative numeric, and money values with the FLOOR function. The result returned is the integer portion of the same datatype as the expression. Note that the money datatype always returns a two-place decimal value but no dollar sign ($).

Statement

Result

SELECT FLOOR(123.45)

123

SELECT FLOOR(-123.45)

-124

SELECT FLOOR($123.45)

123.00

B.    CEILING Function

This example shows positive numeric, negative numeric, and money values with the CEILING function. The result returned is the integer portion of the same datatype as the expression. Note that the money datatype always returns a two-place decimal value but no dollar sign ($).

Statement

Result

SELECT CEILING(123.45)

124

SELECT CEILING(-123.45)

-123

SELECT CEILING($123.45)

124.00

C.    The ROUND Function

This example shows rounding and approximations. The result returned is always the same datatype as the expression. Note that the money datatype always returns a two-place decimal value but no dollar sign ($).

Statement

Result

SELECT ROUND(123.4545, 2)

123.4500

SELECT ROUND(123.45, -2)

100.00

With the ROUND function, the last digit is always an estimate:

ROUND(123.9994, 3) = 123.9990

and

ROUND(123.9995, 3) = 124.0000