Rounding the use of the round Function and cast and convert functions, sqlserverround

Rounding the use of the round Function and cast and convert functions, sqlserverround

Introduction

I encountered a problem when I communicated with the test about a percentage calculation method today. I used a strong conversion CAST (32.678 AS DECIMAL (5, 1) in the stored procedure )) in my opinion, this method will only retain one decimal place. The reply I gave to the test was that I did not use the rounding function, and the data was not rounded, the test said that the data they had verified was rounded down. I tried every computing point in the stored procedure to find this problem.

ROUND

If the ROUND function is used and the decimal point is retained, the ROUND function is preferred. If the field data type is decimal (), there will be many zeros after rounding.

CAST and CONVERT

In fact, I didn't plan to rounding out the results when I used the strong transfer. I just found that these two strong turns would also be rounding out the results to get the data that meets my requirements, that is to say, the following three statements will return the same result value.

select ROUND(32.678,1)  --32.700select CAST(32.678 as DECIMAL(5,1)) --32.7select convert(NUMERIC(5,1),32.678) --32.7

The following example shows the SQL rounding ROUND function.

Question 1:

SELECT CAST('123.456' as decimal) 

123 will be obtained (the decimal point will be omitted ).

If you want to get two digits after the decimal point.

You need to change the above

SELECT CAST('123.456' as decimal(38, 2)) ===>123.46

Auto rounding!

Question 2:

SELECT ROUND(123.75633, 2, 1), ROUND(123.75633, 2)

The two values obtained from the preceding SQL statement are different. The first one is 123.75000, and the last one is 123.76000.

Because the former is truncated after the decimal point before rounding, and retains two digits.

While the latter is not intercepted, it will naturally get 123.76000

ROUND

Returns a numeric expression rounded to the specified length or precision.

Syntax

ROUND (numeric_e-xpression, length [, function])

Parameters

Numeric_e-xpression

Expression of the exact or approximate numeric data type category (except for the bit data type ).

Length

Is the precision of the numeric_e-xpression to be rounded. The length must be tinyint, smallint, or int. When length is a positive number, the numeric_e-xpression is rounded to the decimal point specified by length. When length is negative, the numeric_e-xpression is rounded to the left of the decimal point specified by length.

Function

Is the operation type to be executed. The function must be tinyint, smallint, or int. If the value of function or function is omitted is 0 (default), The numeric_e-xpression is rounded in. Numeric_e-xpression is truncated when a value other than 0 is specified.

Return type

Returns the same type as the numeric_e-xpression.

Note

ROUND always returns a value. If length is a negative number and it is greater than the number before the decimal point, ROUND returns 0.

Sample result

ROUND (748.58,-4) 0

When length is negative, ROUND returns a rounding numeric_e-xpression regardless of the data type.

Sample result

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

Example

A. Use the ROUND and estimated values

The following example shows two expressions, indicating that the ROUND function is used and the last number is always an estimate.

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

The following is the result set:

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

B. Use the approximate values of ROUND and rounding

The following example shows rounding and approximate values.

Statement result

Select ROUND (123.4545, 2)
123.4500
Select ROUND (123.45,-2)
100.00

C. Use ROUND to intercept

In the following example, two Select statements are used to explain 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.00Select ROUND(150.75, 0, 1)150.00