
Latest SQL Server Tips
Free SQL Server Learning
Is HA pitting SQL Database Admins against VM Admins? Keeping the Peace with SANLess clusters. 

How to Roll your Own Value, RegEx and SoundEx Pattern Profiler in SSIS 

By: Jeremy Kadlec  Read Comments (15)  Related Tips: More > Functions  System 
I saw your recent tip on Calculating Mathematical Values in SQL Server and have some related issues as I try to round values in my application. My users and me have a difference of opinion on some of the calculations in our reporting applications. All of the code is in TSQL, but I think the reporting issues are related to data types and rounding down or rounding up rules. Do you have any insight into these issues? I would like to see some examples with a variety of coding options.
Rounding can become misunderstood if the underlying data types and rounding functions are not understood. Depending on the data type (integer, float, decimal, etc.) the rounded value can be different. In addition, depending on the SQL Server rounding function (ROUND(), CEILING(), FLOOR()) used in the calculation the values can differ as well. As such, it is important to find out the user rounding requirements then translate those requirements into the appropriate TSQL command.
From a definition perspective, let's start here:
Let's walk through each function with a few different data types to see the results.
Example 1a  In this first example let's just look at rounding a positive integer for the precision value of 1 yields all three rounding functions returning the same value. In this example we are using a variable with the functions and check out the result commented out on the right of the function.
DECLARE @value int SET @value = 6 SELECT ROUND(@value, 1)  6  No rounding with no digits right of the decimal point SELECT CEILING(@value)  6  Smallest integer value SELECT FLOOR(@value)  6  Largest integer value
Example 1b  Since the CEILING AND FLOOR functions do not have any optional values, let's test some options with the ROUND function. In this example, let's see the impacts of a negative number as the precision as well as the specifying additional positions that exceed the value to round. Check out these results with the result commented out on the right of the function.
DECLARE @value int SET @value = 6 SELECT ROUND(@value, 1)  6  No rounding with no digits right of the decimal point SELECT ROUND(@value, 1)  10  Rounding up with digits on the left of the decimal point SELECT ROUND(@value, 2)  6  No rounding with no digits right of the decimal point SELECT ROUND(@value, 2)  0  Insufficient number of digits SELECT ROUND(@value, 3)  6  No rounding with no digits right of the decimal point SELECT ROUND(@value, 3)  0  Insufficient number of digits
Example 1c  Let's expand the digits in this example with the ROUND function and see the impacts with the result commented out on the right of the function.
SELECT ROUND(444, 1)  444  No rounding with no digits right of the decimal point SELECT ROUND(444, 1)  440  Rounding down SELECT ROUND(444, 2)  444  No rounding with no digits right of the decimal point SELECT ROUND(444, 2)  400  Rounding down SELECT ROUND(444, 3)  444  No rounding with no digits right of the decimal point SELECT ROUND(444, 3)  0  Insufficient number of digits SELECT ROUND(444, 4)  444  No rounding with no digits right of the decimal point SELECT ROUND(444, 4)  0  Insufficient number of digits SELECT ROUND(555, 1)  555  No rounding with no digits right of the decimal point SELECT ROUND(555, 1)  560  Rounding up SELECT ROUND(555, 2)  555  No rounding with no digits right of the decimal point SELECT ROUND(555, 2)  600  Rounding up SELECT ROUND(555, 3)  555  No rounding with no digits right of the decimal point SELECT ROUND(555, 3)  1000  Rounding up SELECT ROUND(555, 4)  555  No rounding with no digits right of the decimal point SELECT ROUND(555, 4)  0  Insufficient number of digits SELECT ROUND(666, 1)  666  No rounding with no digits right of the decimal point SELECT ROUND(666, 1)  670  Rounding up SELECT ROUND(666, 2)  666  No rounding with no digits right of the decimal point SELECT ROUND(666, 2)  700  Rounding up SELECT ROUND(666, 3)  666  No rounding with no digits right of the decimal point SELECT ROUND(666, 3)  1000  Rounding up SELECT ROUND(666, 4)  666  No rounding with no digits right of the decimal point SELECT ROUND(666, 4)  0  Insufficient number of digits
Example 1d  Let's round a negative integer and see the impacts with the result commented out on the right of the function.
SELECT ROUND(444, 1)  440  Rounding down SELECT ROUND(444, 2)  400  Rounding down SELECT ROUND(555, 1)  560  Rounding up SELECT ROUND(555, 2)  600  Rounding up SELECT ROUND(666, 1)  670  Rounding up SELECT ROUND(666, 2)  700  Rounding up
Example 1e  In our last example of this section, do not get fooled by your data types and actual values. In this example, the @value parameter is declared as an INT, but the value passed looks more like a decimal. Under these circumstances, let's see how the values are evaluated.
DECLARE @value int SET @value = 16.999999 SELECT ROUND(@value, 1)  16  No rounding with no digits right of the decimal point i.e. int SELECT ROUND(@value, 1)  20  Round up SELECT CEILING(@value)  16  Smallest integer value SELECT FLOOR(@value)  16  Largest integer value SELECT @value  16  Shows how the @value is evaluated based on the int data type
Example 2a  With a decimal data type and the ROUND function with various length parameters (i.e. 1, 2 or 3) yields different final values in our example. The 5 in the second digit to the right of the decimal point is significant when the length parameter is 1 when rounding the value. In addition, with the decimal data type the CEILING and FLOOR functions take the decimal places into consideration for differing values as well.
DECLARE @value decimal(10,2) SET @value = 11.05 SELECT ROUND(@value, 1)  11.10 SELECT ROUND(@value, 1)  10.00 SELECT ROUND(@value, 2)  11.05 SELECT ROUND(@value, 2)  0.00 SELECT ROUND(@value, 3)  11.05 SELECT ROUND(@value, 3)  0.00 SELECT CEILING(@value)  12 SELECT FLOOR(@value)  11 GO
Example 2b  Here is a quick example of using the numeric data type with the ROUND function. This follows much of the same behavior as the decimal data type.
DECLARE @value numeric(10,10) SET @value = .5432167890 SELECT ROUND(@value, 1)  0.5000000000 SELECT ROUND(@value, 2)  0.5400000000 SELECT ROUND(@value, 3)  0.5430000000 SELECT ROUND(@value, 4)  0.5432000000 SELECT ROUND(@value, 5)  0.5432200000 SELECT ROUND(@value, 6)  0.5432170000 SELECT ROUND(@value, 7)  0.5432168000 SELECT ROUND(@value, 8)  0.5432167900 SELECT ROUND(@value, 9)  0.5432167890 SELECT ROUND(@value, 10)  0.5432167890 SELECT CEILING(@value)  1 SELECT FLOOR(@value)  0
Example 2c  In the final example, with a float data type you can see the same type of behavior as was the case with the decimal and numeric examples above with the ROUND, CEILING and FLOOR functions.
DECLARE @value float(10) SET @value = .1234567890 SELECT ROUND(@value, 1)  0.1 SELECT ROUND(@value, 2)  0.12 SELECT ROUND(@value, 3)  0.123 SELECT ROUND(@value, 4)  0.1235 SELECT ROUND(@value, 5)  0.12346 SELECT ROUND(@value, 6)  0.123457 SELECT ROUND(@value, 7)  0.1234568 SELECT ROUND(@value, 8)  0.12345679 SELECT ROUND(@value, 9)  0.123456791 SELECT ROUND(@value, 10)  0.123456791 SELECT CEILING(@value)  1 SELECT FLOOR(@value)  0
Monday, November 04, 2013  5:27:02 PM  Gadi  Read The Tip 
Technically, there aren't an "insufficient number of digits" from example 1b. When rounding to the nearest 100 (or 1,000), 6 is just closer to zero. Same thing in 1c; 444 is closer to zero than to 1,000 or 10,000. Good tip and explanation. This is pretty logical overall, but sometimes you really need to stop and think it through. These examples are a great help with that. 
Monday, August 12, 2013  5:00:56 PM  Scott Coleman  Read The Tip 
In answer to ClaudioRound's question "why this rounding does not work" (for 172.765). The code "DECLARE @value FLOAT = 172.765" stores the binary number 10101100.110000111101011100001010001111010111000010100, which is about 172.764999999999986. 172.76 is the correct rounded value for something less than 172.765. Subtracting 128 from this value drops the two leftmost bits, so it gains two more fractional bits resulting in 101100.11000011110101110000101000111101011100001010010. (The mantissa is always 53 bits long in a float.) This is about 44.76500000000000057, so even though it has the same fractional digits the value of "ROUND(44.765, 2)" is 44.77. Another fun fact is that "ROUND(CAST(172.7650000000000160090000000000000099 AS FLOAT), 2)" returns 172.76, but if you add a trailing 0 then "ROUND(CAST(172.76500000000001600900000000000000990 AS FLOAT), 2)" returns 172.77. Don't ask me why. The moral of the story is that if you really care about exact fractional values then don't use FLOAT or REAL. Even casting it to DECIMAL before ROUNDING may help. DECLARE @value FLOAT = 172.765 172.77 
Wednesday, June 12, 2013  1:56:53 PM  Dave  Read The Tip 
In Numerical Control programming this is done all the time by using scaling and scaling factors. The scaling factor in this case is 10. DECLARE @TestFloat FLOAT SET @TestFloat = 123.456 SELECT @TestFloat, FLOOR(@TestFloat), FLOOR(@TestFloat)  (FLOOR(@TestFloat / 10.) * 10)

Tuesday, May 28, 2013  10:25:06 PM  Jeremy Kadlec  Read The Tip 
Shri, Can you post some sample data and the expected results? Thank you, 
Friday, May 24, 2013  2:22:50 AM  shri  Read The Tip 
Hello everyone How to get last digit to the left of decimal point in sql query?

Thursday, February 07, 2013  10:46:14 PM  Jeremy Kadlec  Read The Tip 
ClaudioRound, FLOAT and REAL data types are approximate values. I would move to a decimal data type based on the example you provided: DECLARE @value decimal(38,2) HTH. Thank you, 
Monday, February 04, 2013  11:45:05 AM  ClaudioRound  Read The Tip 
why this rounding does not work? DECLARE @value float SET @value = 172.765 SELECT ROUND(@value, 2)

Monday, December 31, 2012  6:56:38 PM  Jeremy Kadlec  Read The Tip 
Everyone, This tip has been updated with additional examples and explanations. Thank you, 
Monday, December 31, 2012  6:55:32 PM  Jeremy Kadlec  Read The Tip 
Sankar, Sorry for my delayed response. Do you just need to write a SELECT or UPDATE statement? What are you trying to do? Have you checked out the following: SELECT Tutorial  http://www.mssqltips.com/sqlservertutorial/10/selectcommandforsqlserver/ HTH. Thank you, 
Thursday, November 15, 2012  1:07:48 AM  Sankar  Read The Tip 
Instead of using cursors is there any other medthod is available. becoz cursor taking more time to fecth records when huge data 
Thursday, November 15, 2012  1:01:59 AM  Sankar  Read The Tip 
Thanks Jeremy for your response i tried that and found to be working.. 
Wednesday, November 14, 2012  8:43:43 AM  Jeremy Kadlec  Read The Tip 
Sankar, If I understand your question correctly, try this code: declare @NumberToBeRounded numeric(29,3)
HTH. Thank you, 
Wednesday, November 14, 2012  1:43:12 AM  Sankar  Read The Tip 
We are using numeric datatype size as 29,9 In this I am facing problem in rounding off any body can help Ex: 1. declare @NumberToBeRounded numeric(29,9) Expected result: 182.360 getting output: 182.350 ( rounding to 0.01)

Thursday, October 18, 2012  5:10:21 PM  Sandeep  Read The Tip 
Thank U. 
Thursday, September 20, 2012  1:06:06 PM  Gene Wirchenko  Read The Tip 
Integers can be rounded. round() will take a negative second argument. e.g. declare @SomeInt int=65536 select ROUND(65536,3) // Result is 66000. 