1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68

---SQL Rounding question 1: SELECT CAST('123.456' as decimal) ---123 You will get 123 (the after the decimal point will be omitted). ---If you want to get two digits after the decimal point. You need to change the above to SELECT CAST('123.456' as decimal(38, 2)) ----123.46---Auto rounding!   ---SQL Rounding question 2: SELECT ROUND(123.75633, 2, 1) ---123.75000 SELECT ROUND(123.75633, 2) ---123.76000 --Because the former has been truncated after the decimal point before rounding, and 2 digits are reserved. --The latter is not intercepted and will naturally get 123 when rounded.76000          ROUND --Returns a numeric expression and rounds to the specified length or precision. ROUND ( numeric_e-xpression , length [ , function ] ) --parameter numeric_e-xpression --An expression for an exact numeric or approximate numeric data type category( bit Except data type).   --length yes numeric_e-xpression The precision to be rounded. length Must be tinyint,smallint or int. --When length When it is positive, numeric_e-xpression Round to length The number of decimal places specified. --When length When it is negative, numeric_e-xpression Then press length The specified is rounded to the left of the decimal point. --function Is the type of operation to perform. function Must be tinyint,smallint or int. If omitted function or function The value of is 0 (default), numeric_e-xpression Round. Truncation occurs when a value other than 0 is specified numeric_e-xpression. --Return type and numeric_e-xpression Same type. --notes ROUND Always return a value. If length Is a negative number and greater than the number before the decimal point, ROUND 0 will be returned. --Example results SELECT ROUND(748.58, -4) --0   When length When it is negative, no matter what data type, ROUND Will return a rounded value numeric_e-xpression.   Example results ROUND(748.58, -1) 750.00 ROUND(748.58, -2) 700.00 ROUND(748.58, -3) 1000.00     Example A. use ROUND And estimates The following example shows two expressions to illustrate the use of ROUND Function and the last number is always an estimate.   Select ROUND(123.9994, 3), ROUND(123.9995, 3) Go   Here is the result set:   ----------- ----------- 123.9990    124.0000    B. use ROUND And rounding approximations The following example shows rounding and approximations.   Statement result Select ROUND(123.4545, 2) 123.4500   Select ROUND(123.45, -2) 100.00     C. use ROUND truncation The following example uses two Select Statement describes the difference between rounding and truncation. The first statement rounds the result. The second statement truncates the result.   Statement result Select ROUND(150.75, 0) 151.00   Select ROUND(150.75, 0, 1) 150.00

---SQL rounding question 1:
Select cast ('123.456 'as decimal) -- 123 will get 123 (the one after the decimal point will be omitted).
---If you want to get two digits after the decimal point. You need to change the above to
Select cast ('123.456 'as decimal (38, 2)) --- 123.46 --- auto rounding!

---SQL rounding question 2:
SELECT ROUND(123.75633, 2, 1) ---123.75000
SELECT ROUND(123.75633, 2) ---123.76000
--Because the former has been truncated after the decimal point before rounding, and 2 digits are reserved.
--The latter is not intercepted and will naturally get 123.76000 when rounded

ROUND -- returns a numeric expression and rounds to the specified length or precision.
ROUND ( numeric_e-xpression , length [ , function ] )
--Parameter numeric_e-xpression
--Expressions for exact or approximate numeric data type categories (except bit data types).

--length is numeric_ The precision to which e-expression will be rounded. length must be tinyint, smallint, or int.
--Numeric when length is a positive number_ E-expression is rounded to the number of decimal places specified by length.
--Numeric when length is negative_ E-expression is rounded to the left of the decimal point as specified by length.
--Function is the type of operation to perform. function Must be tinyint, smallint, or int. If function is omitted or the value of function is 0 (default), numeric_ E-expression rounds. When a value other than 0 is specified, numeric is truncated_ e-xpression.
--Return type and numeric_ E-expression is the same type.
--The comment ROUND always returns a value. If length is negative and greater than the number of digits before the decimal point, ROUND will return 0.
--Example results
SELECT ROUND(748.58, -4) --0

When length is negative, ROUND will return a rounded numeric value regardless of the data type_ e-xpression.

Example results
ROUND(748.58, -1) 750.00
ROUND(748.58, -2) 700.00
ROUND(748.58, -3) 1000.00

Example
A. Use ROUND and estimate
The following example shows two expressions that use the ROUND function and the last number is always an estimate.

Select ROUND(123.9994, 3), ROUND(123.9995, 3)
Go

Here is the result set:

----------- -----------
123.9990 124.0000

B. Use ROUND and rounded approximations
The following example shows rounding and approximations.

Statement result
Select ROUND(123.4545, 2)
123.4500

Select ROUND(123.45, -2)
100.00

C. Use ROUND truncation
The following example uses two Select statements to illustrate the difference between rounding and truncation. The first statement rounds the result. The second statement truncates the result.

Statement result
Select ROUND(150.75, 0)
151.00

Select ROUND(150.75, 0, 1)
150.00