Transact-SQL User's Guide

Home

Transact-SQL User's Guide

Chapter 10: Using the Built-In Functions in Queries

Mathematical Functions

Mathematical built-in functions return values commonly needed for operations on mathematical data.

The mathematical functions take the general form:

function_name(arguments)

The chart below lists the types of arguments that are used in the built-in mathematical functions:

Table 10-7: Arguments used in mathematical functions
Argument Type
Can Be Replaced By
approx_numeric
Any approximate numeric (float, real, or double precision) column name, variable, constant expression, or a combination of these.
integer
Any integer (tinyint, smallint or int) column name, variable, constant expression, or a combination of these.
numeric
Any exact numeric (numeric, dec, decimal, tinyint, smallint, or int), approximate numeric (float, real, or double precision), or money column, variable, constant expression, or a combination of these.
power
Any exact numeric, approximate numeric, or money column, variable, or constant expression, or a combination of these.

**Table 10-7: Arguments used in mathematical functions**
Argument Type	Can Be Replaced By
approx_numeric	Any approximate numeric (float, real, or double precision) column name, variable, constant expression, or a combination of these.
integer	Any integer (tinyint, smallint or int) column name, variable, constant expression, or a combination of these.
numeric	Any exact numeric (numeric, dec, decimal, tinyint, smallint, or int), approximate numeric (float, real, or double precision), or money column, variable, constant expression, or a combination of these.
power	Any exact numeric, approximate numeric, or money column, variable, or constant expression, or a combination of these.

Each function also accepts arguments that can be implicitly converted to the specified type. For example, functions that accept approximate numeric types also accept integer types. Adaptive Server converts the argument to the desired type.

If a function takes more than one expression of the same type, the expressions are numbered (for example, approx_numeric1, approx_numeric2).

Table 10-8 lists the mathematical functions, their arguments, and the results they return:

Table 10-8: Mathematical functions
Function
Argument
Result
abs
(numeric)
Returns the absolute value of a given expression. Results are of the same type, and have the same precision and scale, as the numeric expression.
acos
(approx_numeric)
Returns the angle (in radians) whose cosine is the specified value.
asin
(approx_numeric)
Returns the angle (in radians) whose sine is the specified value.
atan
(approx_numeric)
Returns the angle (in radians) whose tangent is the specified value.
atn2
(approx_numeric1, approx_numeric2)
Returns the angle (in radians) whose tangent is (approx_numeric1/approx_numeric2).
ceiling
(numeric)
Returns the smallest integer greater than or equal to the specified value. Results are of the same type as the numeric expression. For numeric and decimal expressions, the results have a precision equal to that of the expression and a scale of 0.
cos
(approx_numeric)
Returns the trigonometric cosine of the specified angle (in radians).
cot
(approx_numeric)
Returns the trigonometric cotangent of the specified angle (in radians).
degrees
(numeric)
Converts radians to degrees. Results are of the same type as the numeric expression. For numeric and decimal expressions, the results have an internal precision of 77 and a scale equal to that of the expression. When money datatype is used, internal conversion to float may cause loss of precision.
exp
(approx_numeric)
Returns the exponential value of the specified value.
floor
(numeric)
Returns the largest integer that is less than or equal to the specified value. Results are of the same type as the numeric expression. For expressions of type numeric or decimal, the results have a precision equal to that of the expression and a scale of 0.
log
(approx_numeric)
Returns the natural logarithm of the specified value.
log10
(approx_numeric)
Returns the base 10 logarithm of the specified value.
pi
()
Returns the constant value of 3.1415926535897931.
power
(numeric, power)
Returns the value of numeric to the power of power. Results are of the same type as numeric. For expressions of type numeric or decimal, the results have a precision of 77 and a scale equal to that of the expression.
radians
(numeric_expr)
Converts degrees to radians. Results are of the same type as numeric. For expressions of type numeric or decimal, the results have an internal precision of 77 and a scale equal to that of the numeric expression. When the money datatype is used, internal conversion to float may cause loss of precision.
rand
([integer])
Returns a random float value between 0 and 1, using the optional integer as a seed value.
round
(numeric, integer)
Rounds the numeric so that it has integer significant digits. A positive integer determines the number of significant digits to the right of the decimal point; a negative integer, the number of significant digits to the left of the decimal point. Results are of the same type as the numeric expression and, for numeric and decimal expressions, have an internal precision equal to the precision of the first argument plus 1 and a scale equal to that of the numeric expression.
sign
(numeric)
Returns the sign of numeric: positive (+1), zero (0), or negative (-1). Results are of the same type, and have the same precision and scale, as the numeric expression.
sin
(approx_numeric)
Returns the trigonometric sine of the specified angle (measured in radians).
sqrt
(approx_numeric)
Returns the square root of the specified value. Value must be positive or 0.
tan
(approx_numeric)
Returns the trigonometric tangent of the specified angle (measured in radians).

**Table 10-8: Mathematical functions**
Function	Argument	Result
abs	(numeric)	Returns the absolute value of a given expression. Results are of the same type, and have the same precision and scale, as the numeric expression.
acos	(approx_numeric)	Returns the angle (in radians) whose cosine is the specified value.
asin	(approx_numeric)	Returns the angle (in radians) whose sine is the specified value.
atan	(approx_numeric)	Returns the angle (in radians) whose tangent is the specified value.
atn2	(approx_numeric1, approx_numeric2)	Returns the angle (in radians) whose tangent is (approx_numeric1/approx_numeric2).
ceiling	(numeric)	Returns the smallest integer greater than or equal to the specified value. Results are of the same type as the numeric expression. For numeric and decimal expressions, the results have a precision equal to that of the expression and a scale of 0.
cos	(approx_numeric)	Returns the trigonometric cosine of the specified angle (in radians).
cot	(approx_numeric)	Returns the trigonometric cotangent of the specified angle (in radians).
degrees	(numeric)	Converts radians to degrees. Results are of the same type as the numeric expression. For numeric and decimal expressions, the results have an internal precision of 77 and a scale equal to that of the expression. When money datatype is used, internal conversion to float may cause loss of precision.
exp	(approx_numeric)	Returns the exponential value of the specified value.
floor	(numeric)	Returns the largest integer that is less than or equal to the specified value. Results are of the same type as the numeric expression. For expressions of type numeric or decimal, the results have a precision equal to that of the expression and a scale of 0.
log	(approx_numeric)	Returns the natural logarithm of the specified value.
log10	(approx_numeric)	Returns the base 10 logarithm of the specified value.
pi	()	Returns the constant value of 3.1415926535897931.
power	(numeric, power)	Returns the value of numeric to the power of power. Results are of the same type as numeric. For expressions of type numeric or decimal, the results have a precision of 77 and a scale equal to that of the expression.
radians	(numeric_expr)	Converts degrees to radians. Results are of the same type as numeric. For expressions of type numeric or decimal, the results have an internal precision of 77 and a scale equal to that of the numeric expression. When the money datatype is used, internal conversion to float may cause loss of precision.
rand	([integer])	Returns a random float value between 0 and 1, using the optional integer as a seed value.
round	(numeric, integer)	Rounds the numeric so that it has integer significant digits. A positive integer determines the number of significant digits to the right of the decimal point; a negative integer, the number of significant digits to the left of the decimal point. Results are of the same type as the numeric expression and, for numeric and decimal expressions, have an internal precision equal to the precision of the first argument plus 1 and a scale equal to that of the numeric expression.
sign	(numeric)	Returns the sign of numeric: positive (+1), zero (0), or negative (-1). Results are of the same type, and have the same precision and scale, as the numeric expression.
sin	(approx_numeric)	Returns the trigonometric sine of the specified angle (measured in radians).
sqrt	(approx_numeric)	Returns the square root of the specified value. Value must be positive or 0.
tan	(approx_numeric)	Returns the trigonometric tangent of the specified angle (measured in radians).

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 6 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 the section "Conversion Errors".

Some simple examples of mathematical functions follow:

Table 10-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

**Table 10-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:

select round(55.55, -3)

returns a value of 100.00. (The number of zeros to the right of the decimal point is equal to the scale of the first argument's precision plus 1.)


	Expand Search