SQL Server Mathematical Functions

SQL Server Mathematical Functions :

ABS : A mathematical function that returns the absolute (positive) value of the specified numeric expression.

Syntax : ABS ( numeric_expression )

Examples : It shows the results of using the ABS function on three different numbers.

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

Output : 1.0        0.0          1.0

The ABS function can produce an overflow error when the absolute value of a number is greater than the largest number that can be represented by the specified data type. For example, the int data type can hold only values that range from -2,147,483,648 to 2,147,483,647. Computing the absolute value for the signed integer -2,147,483,648 causes an overflow error because its absolute value is greater than the positive range for the int data type.

DECLARE @i int;

SET @i = -2147483648;

SELECT ABS(@i);

GO

Output :

Msg 8115, Level 16, State 2, Line 3

Arithmetic overflow error converting expression to data type int

DEGREES : Returns the corresponding angle in degrees for an angle specified in radians.

Syntax : DEGREES ( numeric_expression )

Examples : It returns the number of degrees in an angle of PI/2 radians.

SELECT 'The number of degrees in PI/2 radians is: ' +

CONVERT(varchar, DEGREES((PI()/2)));

GO

Or

select DEGREES((PI()/2))

Output : The number of degrees in PI/2 radians is: 90

CEILING : Returns the smallest integer greater than, or equal to, the specified numeric expression.

Syntax : CEILING ( numeric_expression )

Examples : It shows positive numeric, negative, and zero values with the CEILING function.

SELECT CEILING($123.45), CEILING($-123.45), CEILING($0.0);

GO

Output:

124.00   -123.00 0.00

PI : Returns the constant value of PI.

Syntax : PI ( )

Return Type: float

Examples : It returns the value of PI.

SELECT PI();

Output: 3.14159265358979

SQUARE : Returns the square of the specified float value.

Syntax : SQUARE ( float_expression )

Return Type: float

Examples :

SELECT SQUARE(5)

Output: 25

SQRT: Returns the square root of the specified float value.

Syntax : SQRT ( float_expression )

Return Type: float

Examples : It returns the square root of numbers between 1.00 and 10.00.

DECLARE @myvalue float;

SET @myvalue = 1.00;

WHILE @myvalue < 10.00

BEGIN

SELECT SQRT(@myvalue);

SET @myvalue = @myvalue + 1

END;

GO

Output:

1.0

------------------------

1.4142135623731         

------------------------

1.73205080756888        

------------------------

2.0                     

------------------------

2.23606797749979        

------------------------

2.44948974278318        

------------------------

2.64575131106459        

------------------------

2.82842712474619        

------------------------

3.0

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

Syntax : FLOOR ( numeric_expression )

Examples : It shows positive numeric, negative numeric, and currency values with the FLOOR function.

SELECT FLOOR(123.45), FLOOR(-123.45), FLOOR($123.45);

Output:

123         -124       123.00