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.
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.
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
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
Returns a numeric expression and rounds it to the specified length or precision.
round (numeric_e-xpression, length [, function])
An expression of the exact numeric or approximate numeric data type category (except for the bit data type).
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.
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.
Returns the same type as numeric_e-xpression.
round always returns a value.If length is negative and greater than the number of digits before the decimal point,round will return 0.
round (748.58, -4) 0
When length is negative,No matter what data type,round will return a rounded numeric_e-xpression.
round (748.58, -1) 750.00
round (748.58, -2) 700.00
round (748.58, -3) 1000.00
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:
b. Use round and rounded approximations
The following example shows rounding and approximation.
select round (123.4545, 2)
select round (123.45, -2)
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.
select round (150.75, 0) 151.00 select round (150.75, 0, 1) 150.00
- mysql start error MySQL server PID file could not be found
- Using NHibernate with SqlServer and MongoDB
- Error: 1326 error (Manager cannot connect to remote database) in SQL Server
- CentOS installation SQL Server vNext CTP1 tutorial
- SQL Server Configuration Manager cannot connect to WMI provider
- SQL Server 2012 installation and startup graphic tutorials
- SqlServer high version data backup and restore to lower version
- String comma separated function sharing in SQL server
- Detailed differences between cast and convert in SQL SERVER
- python - you may need to restart the kernel to use updated packages error
- php - coincheck api authentication doesn't work
- php - i would like to introduce the coincheck api so that i can make payments with bitcoin on my ec site
- [php] i want to get account information using coincheck api
- the emulator process for avd pixel_2_api_29 was killed occurred when the android studio emulator was started, so i would like to
- python 3x - typeerror: 'method' object is not subscriptable
- i want to call a child component method from a parent in vuejs
- xcode - pod install [!] no `podfile 'found in the project directory
- sh - 'apt-get' is not recognized as an internal or external command, operable program or batch file