- Date and Time Functions
- Mathematical Functions
- Text and Image Functions
- Summary
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Mathematical Functions
The large majority of math functions are specific to applications using trigonometry, calculus, and geometry. Because such functions very seldom used in typical business applications, there is only a very light coverage of these functions in this article.
There are, however, a few math functions that deserve more attention and have a more widespread usage. One of these is RAND(), which generates a random number between 0 and 1, expressed as a FLOAT data type. Returning random numbers can be useful for various programming tasks: returning result sets in a random order, for example.
The RAND() function can be executed without any parameters, or you can specify a seed value that can have a TINYINT, SMALLINT, or INT data type. If a seed is specified, RAND()always returns the same number. A trick you might find useful is to pass the current millisecond to the RAND() as the seed, as in the following example:
SELECT RAND( DATEPART(MILLISECOND, GETDATE()) )
NOTE that all mathematical functions are deterministic except the RAND() function used without the seed.
The CEILING() and FLOOR() functions accept a numeric expression as a single parameter and work similar to each other. CEILING() returns the smallest integer value greater than or equal to the parameter, whereas FLOOR() returns the largest integer less than or equal to the parameter. The following example returns the price range for a few titles in the pubs database:
SELECT TOP 4
title_id,
price,
CEILING(price) AS price_ceiling,
FLOOR(price) AS price_floor
FROM titles
Results:
title_id price price_ceiling price_floor -------- ------------- --------------- --------------- BU1032 19.9900 20.0000 19.0000 BU1111 11.9500 12.0000 11.0000 BU2075 2.9900 3.0000 2.0000 BU7832 19.9900 20.0000 19.0000
The ABS() function returns the absolute (positive) value of the given expression. It provides a way to have positive values in the output, even if values within tables are negative. For instance, suppose you are generating an accounting report that must contain credit and debit values in separate columns (both in positive), but your source column for both credit and debit is the balance column. You can generate the report as follows:
DECLARE @account TABLE (
account_number INT,
balance INT)
INSERT @account
VALUES(123, -200)
INSERT @account
VALUES(321, 2)
SELECT
account_number,
'credit' = CASE WHEN balance < 0 THEN ABS(balance) ELSE 0 END,
'debit' = CASE WHEN balance > = 0 THEN balance ELSE 0 END
FROM @account
Results:
account_number credit debit -------------- ----------- ----------- 123 200 0 321 0 2
The following table summarizes the rest of the math functions.
|
Mathematical Function |
Returns |
|
ACOS() |
Angle whose cosine expressed in radians is the expression passed |
|
ASIN() |
Angle whose sine expressed in radians is the expression passed |
|
ATAN() |
Angle whose tangent expressed in radians is the expression passed |
|
ATN2() |
Angle whose tangent expressed in radians is between the two expressions passed |
|
COS() |
Cosine of the given angle in radians |
|
COT() |
Cotangent of the given angle in radians |
|
DEGREES() |
Angle in degrees for the angle provided in radians |
|
EXP() |
Exponential value of the passed expression |
|
LOG() |
Natural logarithm of the passed expression |
|
LOG10() |
Base 10 logarithm of the passed expression |
|
PI() |
Value of PI: Does not accept any parameters |
|
POWER() |
Value of the passed expression into the specified power (as in 2 to the 4th power) |
|
RADIANS() |
Angle in radians for the angle provided in degrees |
|
ROUND() |
Value of the passed parameter, rounded to the specified precision |
|
SIGN() |
+1 for non-negative numeric expressions; (-1) for negative numeric expressions |
|
SIN() |
Sine of the angle provided in radians |
|
SQUARE() |
Square of the given numeric expression |
|
SQRT() |
Square root of the given numeric expression |
|
TAN() |
Tangent of the given angle in radians |