Mathematical Functions in SQL SERVER Part I || ABS, CEILING, FLOOR, POWER functions in SQL 2014
This video will provide you the details of each Mathematical Function in SQL Server 2014.
***********************************************************************
ABS (Transact-SQL)
A mathematical function that returns the absolute (positive) value of the specified numeric expression.
Syntax
ABS ( numeric_expression )
Arguments
numeric_expression
Is an expression of the exact numeric or approximate numeric data type category.
Return Types
Returns the same type as numeric_expression.
Examples
The following example shows the results of using the ABS function on three different numbers.
SELECT ABS(-1.0), ABS(0.0), ABS(1.0);
Here is the result set.
---- ---- ----
1.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);
***********************************************************************
CEILING (Transact-SQL)
Returns the smallest integer greater than, or equal to, the specified numeric expression.
Syntax
CEILING ( 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.
Examples
The following example shows positive numeric, negative, and zero values with the CEILING function.
SELECT CEILING($123.45), CEILING($-123.45), CEILING($0.0);
GO
***********************************************************************
FLOOR (Transact-SQL)
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.
Examples
The following example shows positive numeric, negative numeric, and currency values with the FLOOR function.
SELECT FLOOR(123.45), FLOOR(-123.45), FLOOR($123.45);
***********************************************************************
POWER (Transact-SQL)
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 thebit data type.
Return Types
Returns the same type as submitted in float_expression. For example, if a decimal(2,0) is submitted as float_expression, the result returned isdecimal(2,0).
Examples
A. Using POWER to return the cube of a number
The following example demonstrates raising a number to the power of 3 (the cube of the number).
DECLARE @input1 float;
DECLARE @input2 float;
SET @input1= 2;
SET @input2 = 2.5;
SELECT POWER(@input1, 3) AS Result1, POWER(@input2, 3) AS Result2;