Using Mathematical Functions (Transact-SQL)
A mathematical function performs a mathematical operation on numeric expressions and returns the result of the operation. Mathematical functions operate on the SQL Server system-supplied numeric data: decimal, integer, float, real, money, smallmoney, smallint, and tinyint. By default, the precision of built-in operations on float data type data is six decimal places.
By default, a number passed to a mathematical function will be interpreted as a decimal data type. The CAST or CONVERT functions can be used to change the data type to something else, such as a float. For example, the value returned by the FLOOR function has the data type of the input value. The input of the following SELECT statement is a decimal. FLOOR returns 123. This is a decimal value.
SELECT FLOOR(123.45); --------------------- 123 (1 row(s) affected)
But, the following example uses a float value and FLOOR returns a float value:
SELECT FLOOR (CONVERT (float, 123.45)); ------------------------------------- 123.000000 (1 row(s) affected)
A floating point underflow error occurs when the float or real result of a mathematical function is too small to display. A result of 0.0 is returned and no error message is displayed. For example, the mathematical calculation of 2 to the -100.0 power has a result 0.0.
Domain errors occur when the value provided in the mathematical function is not a valid value. For example, values specified for the ASIN function must be from -1.00 through 1.00. If a range of -2 is specified, a domain error occurs.
Range errors occur when the value specified is outside the allowed values. For example, POWER(10.0, 400) is out of the range of the maximum of ~2e+308 of the float data type, and POWER(-10.0, 401) is out of the range of the minimum of ~ -2e+308 of the float data type.
The following table shows mathematical functions that produce either a domain or range error.
Arithmetic Overflow error.
Value of 0.0 (floating point underflow).
Error traps are provided to handle domain or range errors of these functions. You can use the following:
SET ARITHABORT ON. This terminates the query and ends the user-defined transaction. The SET ARITHABORT setting overrides the setting for SET ANSI_WARNINGS.
SET ANSI_WARNINGS ON. This stops the command.
SET ARITHIGNORE ON. This prevents the display of a warning message. Both the SET ARITHABORT and SET ANSI_WARNINGS settings override the SET ARITHIGNORE setting.
If none of these options is set, SQL Server returns NULL and returns a warning message after the query is executed. For more information:
Internal conversion to float can cause loss of precision if either the money or numeric data types are used.