Mathematical Functions

1. Overview

Mathematical functions perform calculations based on input values provided as parameters to the functions, and return numeric values.

The mathematical functions covered in this section are:

ABS

A mathematical function that returns the absolute (positive) value of the specified numeric expression. (ABS changes negative values to positive values. ABS has no effect on zero or positive values.)

Syntax

ABS ( numeric_expression )

Arguments

numeric_expression An expression of the exact numeric or approximate numeric data type category.

Return Types

Returns the same type as numeric_expression.

Example

This example shows the results of using the ABS function on three different numbers.

SELECT ABS(-5.2), ABS(0.0), ABS(5.2);

ACOS

A function that returns the angle, in radians, whose cosine is the specified float expression. This is also called arccosine.

Syntax

ACOS ( float_expression )

Arguments

float_expression An expression of either type float or of a type that can implicitly convert to float. Only a value ranging from -1.00 to 1.00 is valid. Values outside this range return NULL and ACOS will report a domain error.

Return Types

float

Example

This example returns the ACOS value of the specified angle.

SELECT 'The ACOS of ' + @number + ' is: ' + CONVERT(varchar, ACOS(@number))

ASIN

A function that returns the angle, in radians, whose sine is the specified float expression. This is also called arcsine.

Syntax

ASIN ( float_expression )

Arguments

Return Types

float

Example

This example returns the ASIN value of the specified angle.

SELECT 'The ASIN of ' + @number + ' is: ' + CONVERT(varchar, ASIN(@number))

ATAN

A function that returns the angle, in radians, whose tangent is a specified float expression. This is also called arctangent.

Syntax

ATAN ( float_expression )

Arguments

float_expression An expression of either type float or of a type that implicitly converts to float.

Return Types

float

Example

This example returns the ATAN value of the specified angle.

SELECT 'The ATAN of ' + @number + ' is: ' + CONVERT(varchar, ATAN(@number))

ATN2

Returns the angle, in radians, between the positive x-axis and the ray from the origin to the point (y, x), where x and y are the values of the two specified float expressions.

Syntax

ATN2 ( float_expression , float_expression )

Arguments

float_expression An expression of data type float.

Return Types

float

Example

The following example calculates the ATN2 for the specified x and y components.

SELECT 'The ATAN from the x-axis to point (' + @x + ',' + @y + ') is: ' + CONVERT(varchar, ATN2(@y,@x))

CEILING

This function returns the smallest integer greater than, or equal to, the specified numeric expression.

Syntax

CEILING ( numeric_expression )

Arguments

numeric_expression An expression of the exact numeric or approximate numeric data type category. For this function, the bit data type is invalid

Return Types

Return values have the same type as numeric_expression.

Example

This example shows positive numeric, negative numeric, and zero value inputs for the CEILING function.

SELECT CEILING(1.2), CEILING(-1.2), CEILING(0)

COS

A mathematical function that returns the trigonometric cosine of the specified angle - measured in radians - in the specified expression.

Syntax

COS ( float_expression )

Arguments

float_expression An expression of type float.

Return Types

float

Example

This example returns the COS value of the specified angle.

SELECT 'The COS of ' + @number + ' is: ' + CONVERT(varchar, COS(@number))

COT

A mathematical function that returns the trigonometric cotangent of the specified angle - in radians - in the specified float expression.

Syntax

COT ( float_expression )

Arguments

float_expression An expression of type float, or of a type that can implicitly convert to float.

Return Types

float

Example

This example returns the COT value for the specific angle.

SELECT 'The COT of ' + @number + ' is: ' + CONVERT(varchar, COT(@number))

DEGREES

This function returns the corresponding angle, in degrees, for an angle specified in radians.

Syntax

DEGREES ( numeric_expression )

Arguments

numeric_expression An expression of the exact numeric or approximate numeric data type category, except for the bit data type.

Return Types

Returns a value whose data type matches the data type of numeric_expression.

Example

This example returns the number of degrees in a specified radian.

SELECT 'The number of degrees in ' + @radians + ' radians is: '
       + CONVERT(VARCHAR, DEGREES(@radians))

EXP

Returns the exponential value of the specified float expression.

Syntax

EXP ( float_expression )

Arguments

float_expression Is an expression of type float or of a type that can be implicitly converted to float.

Return Types

float

Example

The following example uses a compounding interest example to illustrate the use of EXP.

SELECT 'With continuous compounding interest, your principal amount of $'
  + @principal + ' will turn into $' 
  + CONVERT(VARCHAR,@principal * EXP(@years * CAST(@interestRate AS FLOAT)))
  +' after ' + @years + ' years at the interest rate of '
  + CONVERT(VARCHAR,CAST(@interestRate AS FLOAT) * 100) + '%'

FLOOR

Returns the largest integer less than or equal to the specified numeric expression.

Syntax

FLOOR ( numeric_expression )

Arguments

numeric_expression Is an expression of the exact numeric or approximate numeric data type category, except for the bit data type.

Return Types

Returns the same type as numeric_expression.

Example

The following example shows positive numeric, negative numeric, and zero value inputs with the FLOOR function.

SELECT FLOOR(1.2), FLOOR(-1.2), FLOOR(0)

LOG

Returns the natural logarithm of the specified float expression in SQL Server.

This function is not currently supported in PostgreSQL deployments of the Cinchy platform.

New function translations are actively being worked on by the development team; please check back at a later time.

You can review the full list of in-progress function translations here.

Syntax

LOG ( float_expression [, base ] )

Arguments

float_expression Is an expression of type float or of a type that can be implicitly converted to float.

base Optional integer argument that sets the base for the logarithm.

Applies to: SQL Server 2012 (11.x) and later

Return Types

float

Remarks

By default, LOG() returns the natural logarithm. Starting with SQL Server 2012 (11.x), you can change the base of the logarithm to another value by using the optional base parameter.

The natural logarithm is the logarithm to the base e, where e is an irrational constant approximately equal to 2.718281828.

The natural logarithm of the exponential of a number is the number itself: LOG( EXP( n ) ) = n. And the exponential of the natural logarithm of a number is the number itself: EXP( LOG( n ) ) = n.

Example

The following example calculates the LOG for a specified number.

SELECT 'The log base ' + @base + ' of ' + @number + ' is: '
  + CONVERT(varchar, LOG(@number,@base))
  
SELECT 'The log of ' + @number + ' is: ' + CONVERT(varchar, LOG(@number))

LOG10

Returns the base-10 logarithm of the specified float expression.

Syntax

LOG10 ( float_expression )

Arguments

float_expression Is an expression of type float or of a type that can be implicitly converted to float.

Return Types

float

Remarks

The LOG10 and POWER functions are inversely related to one another. For example, 10 ^ LOG10(n) = n.

Example 1

Calculating the base 10 logarithm for a variable.

The following example calculates the LOG10 of the specified number.

SELECT 'The log base 10 of ' + @number + ' is: ' + CONVERT(varchar, LOG10(@number))

Example 2

Calculating the result of raising a base-10 logarithm to a specified power.

The following example returns the result of raising a base-10 logarithm to a specified power.

SELECT POWER (10, LOG10(5))

PI

Returns the constant value of PI.

Syntax

PI ( )

Return Types

float

Example

The following example returns the value of PI.

SELECT PI()

POWER

Returns the value of the specified expression to the specified power.

Syntax

POWER ( float_expression , y )

Arguments

float_expression Is an expression of type float or of a type that can be implicitly converted to float.

y Is the power to which to raise float_expression. y can be an expression of the exact numeric or approximate numeric data type category, except for the bit data type.

Return Types

The return type depends on the input type of float_expression:

Input type

Return type

float, real

float

decimal(p, s)

decimal(38, s)

int, smallint, tinyint

int

bigint

money, smallmoney

money

bit, char, nchar, varchar, nvarchar

float

If the result does not fit in the return type, an arithmetic overflow error occurs.

Example

The following example demonstrates raising a specified number to a specified power.

SELECT @x + ' to the power of ' + @y + ' is: ' + CONVERT(VARCHAR, POWER(@x,@y))

RADIANS

Returns radians when a numeric expression, in degrees, is entered.

This function is not currently supported in PostgreSQL deployments of the Cinchy platform.

New function translations are actively being worked on by the development team; please check back at a later time.

You can review the full list of in-progress function translations here.

Syntax

RADIANS ( numeric_expression )

Arguments

numeric_expression Is an expression of the exact numeric or approximate numeric data type category, except for the bit data type.

Return Types

Returns the same type as numeric_expression.

Example

The following example shows the number of radians based on a specified degree.

SELECT @degrees + ' degrees in radians is: ' + CONVERT(VARCHAR, RADIANS(@degrees))

RAND

This function is not currently supported in PostgreSQL deployments of the Cinchy platform.

New function translations are actively being worked on by the development team; please check back at a later time.

You can review the full list of in-progress function translations here.

Returns a pseudo-random float value from 0 through 1, exclusive.

Syntax

RAND ( [ seed ] )

Arguments

seed Is an integer expression (tinyint, smallint, or int) that gives the seed value. If seed is not specified, the SQL Server Database Engine assigns a seed value at random. For a specified seed value, the result returned is always the same.

Return Types

float

Remarks

Repetitive calls of RAND() with the same seed value return the same results.

For one connection, if RAND() is called with a specified seed value, all subsequent calls of RAND() produce results based on the seeded RAND() call. For example, the following query will always return the same sequence of numbers.

SELECT RAND(100), RAND(), RAND()

Example

SELECT RAND(100), RAND(), RAND(5), RAND(), RAND(100), RAND()

ROUND

Returns a numeric value, rounded to the specified length or precision.

Syntax

ROUND ( numeric_expression , length [ ,function ] )

Arguments

numeric_expression Is an expression of the exact numeric or approximate numeric data type category, except for the bit data type.

length Is the precision to which numeric_expression is to be rounded. length must be an expression of type tinyint, smallint, or int. When length is a positive number, numeric_expression is rounded to the number of decimal positions specified by length. When length is a negative number, numeric_expression is rounded on the left side of the decimal point, as specified by length.

function Is the type of operation to perform. function must be tinyint, smallint, or int. When function is omitted or has a value of 0 (default), numeric_expression is rounded. When a value other than 0 is specified, numeric_expression is truncated.

Return Types

Returns the following data types.

Expression result

Return type

tinyint

int

smallint

int

bigint

decimal and numeric category (p, s)

decimal(p, s)

money and smallmoney category

money

float and real category

float

Remarks

ROUND always returns a value. If length is negative and larger than the number of digits before the decimal point, ROUND returns 0.

Examples

Results

ROUND(748.58, -4)

ROUND returns a rounded numeric_expression, regardless of data type, when length is a negative number.

Examples

Restuls

ROUND(748.58, -1)

750.00

ROUND(748.58, -2)

700.00

ROUND(748.58, -3)

Results in an arithmetic overflow, because 748.58 defaults to decimal(5,2), which cannot return 1000.00.

To round up to 4 digits, change the data type of the input. For example: SELECT ROUND(CAST (748.58 AS decimal (6,2)),-3);

1000.00

Example 1

Using ROUND and estimates

The following example shows two expressions that demonstrate by using ROUND the last digit is always an estimate.

SELECT ROUND(123.9994, 3), ROUND(123.9995, 3)

Example 2

Using ROUND and rounding approximations

The following example shows rounding and approximations.

SELECT ROUND(123.4545, 2), ROUND(123.45, -2)

Example 3

Using ROUND to truncate

The following example uses two SELECT statements to demonstrate the difference between rounding and truncation. The first statement rounds the result. The second statement truncates the result.

SELECT ROUND(150.75, 0)

SELECT ROUND(150.75, 0, 1)

SIGN

Returns the positive (+1), zero (0), or negative (-1) sign of the specified expression.

Syntax

SIGN ( numeric_expression )

Arguments

numeric_expression Is an expression of the exact numeric or approximate numeric data type category, except for the bit data type.

Return Types

Specified expression

Return type

bigint

int/smallint/tinyint

int

money/smallmoney

money

numeric/decimal

Other types

float

Example

The following example returns the SIGN values of a positive number, negative number, and zero.

SELECT SIGN(5), SIGN(-5), SIGN(0)

SIN

Returns the trigonometric sine of the specified angle, in radians, and in an approximate numeric, float, expression.

Syntax

SIN ( float_expression )

Arguments

float_expression Is an expression of type float or of a type that can be implicitly converted to float, in radians.

Return Types

float

Example

The following example calculates the SIN for a specified angle.

SELECT 'The SIN of ' + @number + ' is: ' + CONVERT(varchar, SIN(@number))

SQRT

Returns the square root of the specified float value.

Syntax

SQRT ( float_expression )

Arguments

float_expression Is an expression of type float or of a type that can be implicitly converted to float.

Return Types

float

Example

The following example returns the square root of a number.

SELECT 'The square root of ' + @number + ' is: ' + CONVERT(varchar, SQRT(@number))

SQUARE

Returns the square of the specified float value.

Syntax

SQUARE ( float_expression )

Arguments

float_expression Is an expression of type float or of a type that can be implicitly converted to float.

Return Types

float

Example

The following example returns the square of a specified number.

SELECT @number + ' squared (to the power of 2) is: '
  + CONVERT(varchar, SQUARE(@number))

TAN

Returns the tangent of the input expression.

Syntax

TAN ( float_expression )

Arguments

float_expression Is an expression of type float or of a type that can be implicitly converted to float, interpreted as a number of radians.

Return Types

float

Example

The following example returns the tangent of a specified angle.

SELECT 'The TAN of ' + @number + ' is: ' + CONVERT(varchar, TAN(@number))

PreviousLogical Functions NextString Functions

Last updated 1 year ago