The following example returns the variance for all Deptno values in the Dept table.

SELECT VAR(Deptno) FROM Dept;

If VAR is used on all items in a SELECT statement, each value in the result set is included in the calculation.

VAR can be used with numeric columns only.

Null values are ignored.

This function returns NULL if there were no matching rows.

The following example returns the variance for the population for all Deptno values in the Dept table.

SELECT VARP(Deptno) FROM Dept;
            
        

If VARP is used on all items in a SELECT statement, each value in the result set is included in the calculation.

VARP can be used with numeric columns only.

Null values are ignored.

This function returns NULL if there were no matching rows.

The following example returns the standard deviation for all Deptno values in the SalesPerson Dept.

SELECT STDEV(Deptno) FROM Dept;

If STDEV is used on all items in a SELECT statement, each value in the result set is included in the calculation.

STDEV can be used with numeric columns only.

Null values are ignored.

This function returns NULL if there were no matching rows.

The following example returns the standard deviation for the population for all Deptno values in the SalesPerson Dept.

SELECT STDEVP(Deptno) FROM Dept;

If STDEVP is used on all items in a SELECT statement, each value in the result set is included in the calculation.

STDEVP can be used with numeric columns only.

Null values are ignored.

This function returns NULL if there were no matching rows.

The following example shows positive number, negative number, and string argument with the FLOOR function

SELECT FLOOR(12.9), 
SELECT FLOOR(-12.9);
                
SELECT FLOOR('12.9')

For integer arguments, the return value has integer type.

For string or floating-point arguments, the return value has a floating-point type.

Returns NULL if expession is NULL;

Return Types: The floating-point type.

The following example shows positive number, negative number, and string argument with the CEILING function

SELECT CEILING(12.9), 
SELECT CEILING(-12.9);
                
SELECT CEILING('12.9')

For integer arguments, the return value has integer type.

For string or floating-point arguments, the return value has a floating-point type.

Returns NULL if expession is NULL;

Return Types: The floating-point type.

The following example returns POWER results;

SELECT POWER(10.5, 4);
              
SELECT POWER(2,  2.5);

expression is an expression of type floating-point or of a type that can be implicitly converted to floating-point type.

y is a power to which expression will be raised.

Returns NULL if expession is NULL or y is NULL.

Return Types: The type of expression.

The following example returns truncated value to the one digit after decimal point;

SELECT TRUNCATE(12.94, 1)

expression is an expression of type floating-point or of a type that can be implicitly converted to floating-point type.

digits is a value of integer type.

Returns NULL if expession is NULL or digits is NULL.

Return Types: The type of expression.

The following example returns the square root of 16;

SELECT SQRT(16);

expression is an expression of type floating-point or of a type that can be implicitly converted to floating-point type.
Returns NULL if expession is NULL or expession < 0;

Return Types: The type of expression.

The following example returns the COS of the specific angle.

SELECT COS(3.14);

expression is an expression of type floating-point or of a type that can be implicitly converted to floating-point type.

Returns NULL if expession is NULL;

Return Types: The type of expression.

The following example returns the SIN of the specific angle.

SELECT SIN(3.14);

expression is an expression of type floating-point or of a type that can be implicitly converted to floating-point type.

Returns NULL if expession is NULL;

Return Types: The type of expression.

The following example returns the ACOS of the specified number

SELECT ACOS(-1);

expression is an expression of the type float or of a type that can be implicitly converted to floating-point type, with a value from -1 through 1.

Values outside this range return NULL.

Returns NULL if expession is NULL;

Return Types: The type of expression.

The following example returns the ASIN of the specified number

SELECT ASIN(1);

expression is an expression of the type float or of a type that can be implicitly converted to floating-point type, with a value from -1 through 1.

Values outside this range return NULL

Returns NULL if expession is NULL;

Return Types: The type of expression.

The following example returns the ATAN of the specified number

SELECT ATAN(1);

expression is an expression of the type float or of a type that can be implicitly converted to floating-point type.

Returns NULL if expession is NULL;

Return Types: The type of expression.

The following example calculates the ATN2 for the specified x = 1.5 and y =2.0 components

SELECT ATAN(2,1.5);

expression1, expression1 are expressions of the float type or of a type that can be implicitly converted to floating-point type.

Returns NULL if expession1 is NULL or expession2 is NULL;

Return Types: The type of expression.

The following example returns the COT for the specific angle.

SELECT COT(124.1332);

expression is an expression of the type float or of a type that can be implicitly converted to floating-point type.

Returns NULL if expession is NULL;

Return Types: The type of expression.

The following example returns the value of PI:

SELECT PI();

Return Types: floating-point type.

The following example returns the number of degrees in an angle of PI/2 radians:

SELECT DEGREES(PI()/2);

expression is an expression of the type float or of a type that can be implicitly converted to floating-point type.

Returns NULL if expession is NULL;

Return Types: The type of expression.

The following example returns the exponential value of the 10:

SELECT EXP(10);

expression is an expression of the type float or of a type that can be implicitly converted to floating-point type.

The constant e (2.718281?), is the base of natural logarithms.

The exponent of a number is the constant e raised to the power of the number. For example EXP(1.0) = e^1.0 = 2.71828182845905 and EXP(10) = e^10 = 22026.4657948067.

The exponential of the natural logarithm of a number is the number itself: EXP (LOG (n)) = n. And the natural logarithm of the exponential of a number is the number itself: LOG (EXP (n)) = n.

Returns NULL if expession is NULL;

Return Types: The type of expression.

The following example calculates the LOG for the specified number 2.718:

SELECT LOG(2.718);

expression is an expression of the type float or of a type that can be implicitly converted to floating-point type.

The constant e (2.71828182845905?) is the base of natural logarithms.

The base of natural logarithms is the constant e (2.71828182845905?). LOG ( e ) = 1.0.

The natural logarithm of the exponential of a number is the number itself: LOG( EXP( n ) ) = n. And the exponential of the natural logarithm of a number is the number itself: EXP( LOG( n ) ) = n.

Returns NULL if expession is NULL;

Return Types: The type of expression.

The following example calculates the base-2 LOG for the specified number 8:

SELECT LOG(2, 8);

expression is an expression of the type float or of a type that can be implicitly converted to floating-point type.

base is an expression of the type float or of a type that can be implicitly converted to floating-point type.

Returns NULL if expession is NULL;

Return Types: The type of expression.

The following example calculates the LOG10 of the specified value:

SELECT LOG10(100);

expression is an expression of the type float or of a type that can be implicitly converted to floating-point type.

The LOG10 and POWER functions are inversely related to one another. For example, 10 ^ LOG10(n) = n.

Returns NULL if expession is NULL;

Return Types: The type of expression.

The following example returns the number of radians in an angle of 90 degrees:

SELECT DEGREES(90);

expression is an expression of the type float or of a type that can be implicitly converted to floating-point type.

Returns NULL if expession is NULL;

Return Types: The type of expression.

The following example returns the SIGN values of number -2.34:

SELECT SIGN(-2.34);

expression is an expression of the type float or of a type that can be implicitly converted to floating-point type.

Returns NULL if expession is NULL;

Return Types: The integer type.

The following example returns the tangent of 2.5:

SELECT TAN(2.5);

expression is an expression of the type float or of a type that can be implicitly converted to floating-point type, interpreted as number of radians.

Returns NULL if expession is NULL;

Return Types: The type of expression.

The following example returns 1:

SELECT CONTAINS('bc', 'abc');

Returns 1 or 0 to indicate whether 'str' completely contains 'substr'.

Returns NULL when 'substr' or 'str' are NULL.

The following example returns 4:

SELECT INDEXOF('xyz', 'abcxyz', 3);

Returns 0 if 'substr' is not in 'str'.

Returns NULL when 'substr' or 'str' are NULL.

The following example returns 1:

SELECT STARTSWITH('ab', 'abc');

Returns 0 if 'substr' is not in 'str'.

Returns NULL when 'substr' or 'str' are NULL.

The following example returns 1:

SELECT STARTSWITH('bc', 'abc');

Returns 0 if 'substr' is not in 'str'.

Returns NULL when 'substr' or 'str' are NULL.

The following example returns dcba:

SELECT REVERSE('abcd');

Returns 0 if 'substr' is not in 'str'.

Returns NULL when 'substr' or 'str' are NULL.

The following example adding 500 nanoseconds to the specified time:

SELECT
 ADDNANOSECONDS('11:23:52.1234568', 500);

expression is an expression of the string type that represents Datetime, Time or DateTimeOffset value.

100 nanoseconds is equal to 0.0000001 (10^-7) second.

Returns NULL when 'expression' or 'number' is NULL.

Return Value: a string representation of DateTime, Time, DateTimeOffset.

The following example adding 500 microseconds to the specified time:

SELECT
 ADDMICROSECONDS('11:23:52.1234568', 500);

expression is an expression of the string type that represents Datetime, Time or DateTimeOffset value.

Returns NULL when 'expression' or 'number' is NULL.

Return Value: a string representation of DateTime, Time, DateTimeOffset.

The following example adding 500 milliseconds to the specified time:

SELECT
 ADDMILLISECONDS('11:23:52.1234568', 500);

expression is an expression of the string type that represents Datetime, Time or DateTimeOffset value.

Returns NULL when 'expression' or 'number' is NULL.

Return Value: a string representation of DateTime, Time, DateTimeOffset.

TThe following example adding -12 seconds to the specified time:

SELECT
 ADDSECONDS('11:23:52.1234568', -12);

expression is an expression of the string type that represents Datetime, Time or DateTimeOffset value.

Returns NULL when 'expression' or 'number' is NULL.

Return Value: a string representation of DateTime, Time, DateTimeOffset.

The following example adding 10 minutes to the specified time:

SELECT
 ADDMINUTES('11:23:52.1234568', 10);

expression is an expression of the string type that represents Datetime, Time or DateTimeOffset value.

Returns NULL when 'expression' or 'number' is NULL.

Return Value: a string representation of DateTime, Time, DateTimeOffset.

The following example adding 10 minutes to the specified time:

SELECT
 ADDMINUTES ('11:23:52.1234568', 10);

expression is an expression of the string type that represents Datetime, Time or DateTimeOffset value.

Returns NULL when 'expression' or 'number' is NULL.

Return Value: a string representation of DateTime, Time, DateTimeOffset.

The following example adding -5 hours to the specified time:

SELECT ADDHOURS
 ('11:23:52.1234568', -5);

expression is an expression of the string type that represents Datetime, Time or DateTimeOffset value.

Returns NULL when 'expression' or 'number' is NULL.

Return Value: a string representation of DateTime, Time, DateTimeOffset.

The following example adding 5 days to the specified time:

SELECT ADDDAYS
  ('2000-01-01 11:23:52.1234568', 5);

expression is an expression of the string type that represents Datetime, Time or DateTimeOffset value.

Returns NULL when 'expression' or 'number' is NULL.

Return Value: a string representation of DateTime, Time, DateTimeOffset.

The following example adding 5 months to the specified time:

SELECT
 ADDMONTHS('2000-01-01 11:23:52.1234568', 5);

expression is an expression of the string type that represents Datetime, Time or DateTimeOffset value.

Returns NULL when 'expression' or 'number' is NULL.

Return Value: a string representation of DateTime, Time, DateTimeOffset.

The following example adding 5 years to the specified time:

SELECT
 ADDYEARS ('2000-01-01 11:23:52.1234568', 5);

expression is an expression of the string type that represents Datetime, Time or DateTimeOffset value.

Returns NULL when 'expression' or 'number' is NULL.

Return Value: a string representation of DateTime, Time, DateTimeOffset.

The following example return new datetime value:

SELECT CREATEDATETIME(2000,01,01, 11, 23, 52);

year, month, day, hour, minute, second are values of integer type or values of a type that can be implicitly converted to integer type, Returns NULL when any of arguments is NULL.

Return Value: a string representation of DateTime.

The following example return new datetimeoffset value:

SELECT CREATEDATETIME(2000,01,01, 11, 23, 52);

year, month, day, hour, minute, second, tzoffset are values of integer type or values of a type that can be implicitly converted to integer type, tzoffset = between from -840 to 840 minutes (-14 to +14 hours)

Returns NULL when any of arguments is NULL.

Return Value: a string representation of DateTimeOffset.

The following example return new time value:

SELECT CREATETIME(11, 23, 52);

hour, minute, second are values of integer type or values of a type that can be implicitly converted to integer type.

Returns NULL when any of arguments is NULL.

Return Value: a string representation of Time.

The following example return current datetimeoffset value:

SELECT CURRENTDATETIMEOFFSET();

Return Value: a string representation of Time.

The following example return time interval between two datetime values:

SELECT DATETIMEINTERVAL('2000-01-01 11:23:52', 
                '2005-06-20 12:24:54');

startExpression, endExpression are expressions of the string type that represents Datetime, Time or DateTimeOffset value.

Returns NULL when 'startExpression' or 'endExpression' is NULL.

Return Value: a string representation of time interval.

The following example return number of months between two datetime values:

SELECT DIFFMONTHS('2000-01-01 11:23:52',
                '2005-06-20 12:24:54');

startExpression, endExpression are expressions of the string type that represents Datetime, Time or DateTimeOffset value.

Returns NULL when 'startExpression' or 'endExpression' is NULL.

Return Value: a integer.

The following example return number of years between two datetime values:

SELECT DIFFYEARS('2000-01-01 11:23:52',
                '2005-06-20 12:24:54');

startExpression, endExpression are expressions of the string type that represents Datetime, Time or DateTimeOffset value.

Returns NULL when 'startExpression' or 'endExpression' is NULL.

Return Value: a integer.

The following example return offset minutes from specified datetimeoffset:

SELECT GETTOTALOFFSETMINUTES(
                '2000-01-01 11:23:52 +05:30');

datetimeoffset is an expression of the string type that represents DateTimeOffset value.

Returns NULL when 'datetimeoffset' is NULL.

The following example return truncated datetime:

SELECT TRUNCATE('2000-01-01 11:23:52');

expression is an expression of the string type that represents Datetime, Time or DateTimeOffset value.

Returns NULL when 'expression' is NULL.

Return Type: a string representation date.

The following example return bitwise conjunction of two integer numbers:

SELECT BITWISEAND(2,5);

x_expr, y_expr are expressions of the integer types or expressions of a type that can be implicitly converted to integer types.

Returns NULL when any expression is NULL.

Return Types: The type of expressions.

The following example return bitwise disjunction of two integer numbers:

SELECT BITWISEOR(2,5);

x_expr, y_expr - are expressions of the integer types or expressions of a type that can be implicitly converted to integer types.

Returns NULL when any expression is NULL.

Return Types: The type of expressions.

The following example return bitwise exclusive disjunction of two integer numbers:

SELECT BITWISEXOR(2,5);

x_expr, y_expr are expressions of the integer types or expressions of a type that can be implicitly converted to integer types.

Returns NULL when any expression is NULL.

Return Types: The type of expressions.

The following example return bitwise bitwise negation integer number:

SELECT BITWISENOT(5);

x_expr is an expression of the integer types or expression of a type that can be implicitly converted to integer types.

Returns NULL when expression is NULL.

Return Types: The type of expression.

The following example return bitwise bitwise negation integer number:

SELECT NEWGUID();