SQL Server rounding uses the round function and the cast and convert functions _mssql

Introduction

Today and test communication a percent calculation method encountered a problem, I used a strong cast in the stored procedure (32.678 as DECIMAL (5,1)) I think the method will only hold a decimal number, and my response to the test is that I didn't use the rounding function and the data was not rounded, The test said that the data they had validated themselves was rounded. I thought about it. So I tried to test every point in the stored procedure before I discovered the problem.

ROUND

Then we would definitely prefer the round function when rounding and keeping the decimal point, if the field data type is decimal (18,10) Then there will be a lot of 0 appear.

Cast and convert

In fact, I do not intend to round the results of the use of the strong, but simply to get the data to meet my requirements, today found that these two strong turn will be rounded results, that is to say, the following three statements will return the same result value

Select ROUND (32.678,1)  --32.700
Select CAST (32.678 as DECIMAL (5,1))--32.7
Select CONVERT (NUMERIC), 32.678)--32.7

Let's take a short time to introduce SQL rounding round functions

SQL Rounding 2007/11/01 16:35 question 1:

Will get 123 (will be omitted after the decimal point).

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

You need to change the above

SELECT CAST (' 123.456 ' as Decimal (2)) ===>123.46

It's automatically rounded!

Question 2:

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

The above SQL obtained 2 values are not the same, the previous one is: 123.75000, the latter one is: 123.76000.

Because before rounding, the former has been intercepted after the decimal point, retaining 2 digits.

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

ROUND

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

Grammar

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

Parameters

Numeric_e-xpression

An expression of an exact numeric or approximate numeric data type category (except for the bit data type).

Length

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

function

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

return type

Returns the same type as numeric_e-xpression.

Comments

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

Sample Results

ROUND (748.58,-4) 0

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

Sample Results

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

Example

A. Use of ROUND and estimated values

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. Using approximate values for ROUND and rounding

The following example shows rounding and approximation values.

Statement results

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

C. Using ROUND truncation

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

Statement results

Select ROUND (150.75, 0)
151.00
Select ROUND (150.75, 0, 1)
150.00