round function

The round function rounds the value of an input expression to the specified decimal places to the right of the decimal point.

Syntax:

double round(n [, d ])

Semantics:

• n, d: The arguments n and d are expressions that resolve to numbers. The value of d must be in the range of -30 <=d<=30. Any value of d exceeding the maximum absolute value of 30 is truncated to 30 or -30 respectively. If d is not an integer, it is cast to an integer value.

  For example, round(123.456,1.5) is considered round(123.456,1), and round(123.456,50) is treated as round(123.456,30).

• return type: double. Depending on the values of n and d, the round function behaves as follows:

  • If d is a positive value, the function rounds n to d places on the right of the decimal point. If the fractional part of n is 0.5 or greater, the value is rounded away from 0.

    For example, round(0.5) returns 1.0 and round(-0.5) returns -1.0.

  • If d is greater than or equal the number of digits to the right of the decimal point in n, the value of n is returned.

  • If d is not specified or zero, the function rounds n to zero decimal places.

  • If d is a negative value, the function rounds n to the left of the decimal point. If the number of digits in n to the left of the decimal point is less than or equal to the absolute value of d, then the function returns 0.0.

  • Returns zero if n is zero, regardless of the value of d (except when d is NULL).

  • Returns a NULL value in the following cases:

    • If either of the arguments is a NULL value

    • If either of the arguments does not resolve to a number

  • Returns n if either of the arguments resolves to a NaN or infinity.

Example 1 - round function

SELECT round(n,d) FROM Userstocks

Value of n	Value of d	Output
100.331	2	100.33
100.367	2	100.37
111.567	0.5	112.0
111.567	4	111.567
10.361	0 or not specified	10.0
111.331	-2	100.0
111.331	-4	0.0
-100.331	2	-100.33
-111.331	-2	-100.0