Working With Expressions in a Computed Property
Using Mathematical Functions
The following table lists the mathematical functions that can be used in building an expression for a Computed Property:
| Function | Description | Example |
|---|---|---|
|
ABS(number) |
Returns the absolute, positive value of the given numeric expression. |
ABS(-1.0) = 1.0 ABS(0.0) = 0 ABS(1.0) = 1.0 |
|
ACOS(float) |
Returns the angle in radians whose cosine is the given float expression (also called arccosine). |
ACOS(-1) = 3.14159 |
|
ASIN(float) |
Returns the angle in radians whose sine is the given float expression (also called arcsine). |
ASIN(1) = 1.5707963267948966 |
|
ATAN(float) |
Returns the angle in radians whose tangent is the given float expression (also called arctangent). |
ATAN(0) = 0 ATAN(1) = 0.78539816339744828 |
|
ATN2(float,float) |
Returns the angle in radians whose tangent is between the two given float expressions (also called arctangent). |
ATN2(1,1) = 0.78539816339744828 ATN2(2,1) = 1.1071487177940904 |
|
AVG(expr) |
Returns the average of the values in a group. Null values are ignored. |
AVG(Prop1) from Accounts where Account ID < 5 AVG(Prop1) = (4+5+8+6+2) / 5 = 5 |
|
CEILING(number) |
A mathematical function that returns the smallest integer greater than or equal to the given numeric expression. |
CEILING(1) = 1 CEILING(1.23) = 2 CEILING(-1.23) = -1 |
|
COS(float) |
A mathematical function that returns the trigonometric cosine of the given angle (in radians) in the given expression. |
COS(0) = 1 COS(1) = 0.54030230586813977 |
|
COT(float) |
A mathematical function that returns the trigonometric cotangent of the specified angle (in radians) in the given float expression. |
COT(1) = 0.64209261593433076 COT(0.5) = 1.830487721712452 |
|
DEGREES(number) |
Given an angle in radians, returns the corresponding angle in degrees. |
DEGREES(pi()/2) = 90 |
|
EXP(float) |
Returns the exponential value of the given float expression. |
EXP(0) = 1 EXP(1) = 2.7182818284590451 |
|
FLOOR(number) |
Returns the largest integer less than or equal to the given numeric expression. |
FLOOR(123.45) = 123 FLOOR(-123.45) = -124 |
|
LOG(float) |
Returns the natural logarithm of the given float expression. |
LOG(1) = 0 LOG(2) = 0.69314718055994529 |
|
LOG10(float) |
Returns the base-10 logarithm of the given float expression. |
LOG(2) = 0.3010299956639812 LOG(5) = 0.69897000433601886 |
|
PI() |
Returns the constant value of PI. |
PI() = 3.1415926535897931 |
|
POWER(number, y) |
Returns the value of the given expression to the specified power. |
POWER(2,4) = 16 POWER(5,3) =125 |
|
RADIANS(number) |
Returns radians given a numeric expression in degrees. |
RADIANS(90) = 1 RADIANS(0) = 0 |
|
RAND([seed]) |
Returns a random float value between 0 and 1. |
RAND(1) = 0.71359199321292355RAND(0) = 0.94359739042414437 |
|
ROUND(number, length) |
Returns a numeric expression, rounded to the specified length or precision. |
ROUND(123.9994, 3) = 123.9990 ROUND(123.9995, 3) = 124.0000 |
|
SIGN(number) |
Returns the positive (+1), zero (0), or negative (-1) sign of the given expression. |
SIGN(1) = 1 SIGN(0) = 0 SIGN(-1) = -1 |
|
SIN(float) |
Returns the trigonometric sine of the given angle (in radians) in an approximate numeric (float) expression. |
SIN(1) = 0.8414709848078965 SIN(0) = 0 |
|
SQUARE(float) |
Returns the square of the given expression. |
SQUARE(2) = 4 SQUARE(-4) = 16 |
|
SQRT(float) |
Returns the square root of the given expression. |
SQRT(4) = 2 SQRT(64) = 8 |
|
SUM(expr) |
Returns the sum of all the values in the expression. SUM can be used with numeric columns only. Null values are ignored. |
SUM(Prop1) from Accounts where Account ID < 5 SUM(Prop1) = (4+5+8+6+2) = 25 |
|
TAN(float) |
Returns the trigonometric tangent of the given angle (in radians) in an approximate numeric (float) expression. |
TAN(45) = 1.6197751905438615 TAN(0) = 0 |
Using Date and Time Functions
The following table lists the date time functions that can be used in building an expression for a Computed Property:
| Function | Description | Example |
|---|---|---|
|
DATEPART(datepart, date) |
Returns an integer that represents the datepart of the specified date. |
If date is 2003/01/31 DATEPART(d,date) = 31 DATEPART(m,date) = 01 DATEPART(y,date) = 2003 |
|
DAY(date) |
Returns an integer that represents the day datepart of the specified date. |
DAY('01/27/2003') = 27 |
|
GETDATE |
Returns the current system date and time in the Microsoft® SQL Server™ standard internal format for datetime values. |
GETDATE() |
|
MONTH(date) |
Returns an integer that represents the month part of a specified date. |
MONTH('01/27/2003') = 01 |
|
YEAR(date) |
Returns an integer that represents the year part of a specified date. |
YEAR('01/27/2003') = 2003 |
Datepart Abbreviations
The following table lists Datepart abbreviations:
| Datepart | Abbreviation |
|---|---|
| year | yy, yyyy |
| quarter | qq, q |
| month | mm, m |
| dayofyear | dy, y |
| day | dd, d |
| week | wk, ww |
| hour | hh |
| minute | min |
| second | ss, s |
| millisecond | ms |