Home>

### sqlserver rounding using round function and cast and convert functions

When you encounter sqlserver rounding, is there any other way besides using the round function?Now let ’s introduce the use of the cast and convert functions. Learn with friends who are interested.

introduction

I encountered a problem today when communicating a percentage calculation method with the test. I used cast cast (32.678 as decimal (5,1)) in the stored procedure. I think this method will only retain one decimal place.My response to the test was that I did not use the rounding function,The data is not rounded,The test said that they themselves verified the data after rounding. Thinking of this, I then tried every calculation point of the stored procedure,Just found out the problem.

round

Then when rounding is used and the decimal point is retained, we will definitely prefer the round function. If the data type of the field is decimal (18,10), then there will be many zeros after rounding.

cast and convert

Actually, I did n’t intend to round the result when I used the strong rotation.Just to get the data that meets my requirements,Today I discovered that these two strong turns will also round off the results,In other words, 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 (5,1), 32.678) --32.7
``````

Let's take a moment to introduce the rounding round function of SQL

SQL rounding 2007/11/01 16:35 Question 1:

``````select cast ("123.456" as decimal)
``````

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

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

You need to change the above to

``````select cast ("123.456" as decimal (38, 2)) ===>123.46
``````

Automatic rounding!

Question 2:

``````select round (123.75633, 2, 1), round (123.75633, 2)
``````

The two values ​​obtained by the above sql are different.The former is:123.75000, the latter is:123.76000.

Because the former before rounding,Has been intercepted after the decimal point,2 digits reserved.

The latter was not intercepted,When rounding, you will naturally get 123.76000

round

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

grammar

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

parameter

numeric_e-xpression

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

length

Is the precision to which numeric_e-xpression will be rounded.length must be tinyint, smallint, or int. When length is positive,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 be performed.function must be tinyint, smallint, or int. If function is omitted or the value of function is 0 (the default), numeric_e-xpression is rounded.When a value other than 0 is specified,Numeric_e-xpression will be truncated.

Return type

Returns the same type as numeric_e-xpression.

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 result

round (748.58, -4) 0

When length is negative,No matter what data type,round will return a rounded numeric_e-xpression.

Example result

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,The round function is used 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 approximation.

Statement result

select round (123.4545, 2)

123.4500

select round (123.45, -2)

100.00

c. Truncate using round

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

Statement result

``````select round (150.75, 0)
151.00
select round (150.75, 0, 1)
150.00
``````
• Previous Android programming method for SMS reading and saving to SQLite
• Next Analysis of basic concepts related to strings in Swift language