Friday, June 8, 2007

SQL Mathematic Functions

It perform a calculation, usually based on input values and return a numeric value.
Below is All built-in Sql Mathematic Function :
ABS, DEGREES, RAND, ACOS, EXP, ROUND, ASIN, FLOOR, SIGN, ATAN, LOG, SIN, ATN2, LOG10, SQUARE, CEILING, PI, SQRT, COS, POWER, TAN, COT, RADIANS

Example SQL Mathematic Functions - ABS
- Returns the absolute and positive value.
Syntax - ABS ( numeric )
SELECT ABS(-1.0) -- Value = 1.0
SELECT ABS(0.0) -- Value = 0.0
SELECT ABS(1.0) -- Value = 1.0


Example SQL Mathematic Functions - DEGREES
- returns angle in degrees from radian value.
Syntax - DEGREES( numeric)
SELECT DEGREES(1) -- Value = 57 degrees
SELECT DEGREES(2) -- Value = 114 degrees


Example SQL Mathematic Functions - FLOOR
- Returns the largest integer less than or equal to the given numeric expression.
Syntax - FLOOR( numeric)

SELECT FLOOR(32.15) -- Value = 32
SELECT FLOOR(-32.15) -- Value = -33
SELECT FLOOR($32.15) -- Value = 32.0000


Example SQL Mathematic Functions - CEILING
- Returns the smallest integer greater than, or equal to the input value
Syntax - CEILING( numeric)
SELECT CEILING(32.15) -- Value = 33
SELECT CEILING(-32.15) -- Value = -32
SELECT CEILING($32.15) -- Value =
33.0000


Example SQL Mathematic Functions - ROUND
- Return rounded value.
Syntax - ROUND( numeric, length)
SELECT ROUND(32.15 ,1) -- Value = 32.20
SELECT ROUND(32.14 ,1) -- Value = 32.10
SELECT ROUND(32.14998 ,4) -- Value = 32.15000
SELECT ROUND(32.14992 ,4) -- Value = 32.14990
SELECT ROUND(32.92 ,0) -- Value = 33.00


Example SQL Mathematic Functions - POWER
- Returns the value of input to the specified power.
Syntax - POWER( numeric, power(integer))

SELECT POWER(6,2) -- Value = 36 (6*6)
SELECT POWER(9,3) -- Value = 729 (9*9*9)