SQL Server Rounding Functions - Round, Ceiling and Floor

By:   |   Updated: 2021-07-20   |   Comments (22)   |   Related: 1 | 2 | 3 | 4 | > Functions System


Problem

I saw your recent tutorial on Calculating Mathematical Values in SQL Server and have some related issues as I try to round values in my stored procedures and scripts. My users and me have a difference of opinion on some of the calculations in our reporting applications. All of the code is in T-SQL, 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.

Solution

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 T-SQL command.

From a definition perspective for the mathematical functions, let's start here:

  • ROUND - Rounds a positive or negative value to a specific length and accepts three values:
    • Value to round
      • Positive or negative number
      • This data type can be an int (tiny, small, big), decimal, numeric, money or smallmoney
    • Precision when rounding
      • Positive number rounds on the right side of the decimal point
      • Negative number rounds on the left side of the decimal point
    • Truncation of the value to round occurs when this value is not 0 or not included
  • CEILING - Evaluates the value on the right side of the decimal and returns the smallest integer greater than, or equal to, the specified numeric expression and accepts one value:
    • Value to round
  • FLOOR - Evaluates the value on the right side of the decimal and returns the largest integer less than or equal to the specified numeric expression and accepts one value:
    • Value to round

Let's walk through each function with a few different data types to understand the T-SQL syntax and see the results.

Microsoft SQL Server ROUND, CEILING and FLOOR Examples for Integer Data Types

Example 1a - In the following 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 called by a SELECT statement 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

Microsoft SQL Server ROUND, CEILING and FLOOR Examples for Decimal, Numeric and Float Data Types

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 for the numeric value as the decimal value.

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
Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Jeremy Kadlec Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2021-07-20

Comments For This Article




Tuesday, February 15, 2022 - 4:22:05 PM - Jeremy Kadlec Back To Top (89794)
Maca_Andy,

I am not sure I have a complete picture of what you are looking for, but I am trying to put the pieces of the puzzle together.

Try this code T-SQL:

DECLARE @value decimal(10,2)
SET @value = 1.13

SELECT CASE WHEN @value >= 1.00 and @value <= 1.12 THEN 1.00
WHEN @value >= 1.13 and @value <= 1.25 THEN 1.25
-- Add the rest of your logic...
ELSE 1.5
END

Thank you,
Jeremy Kadlec
Community Co-Leader

Tuesday, February 15, 2022 - 11:41:24 AM - Maca_Andy Back To Top (89790)
Hello! Can somebody help me..
i want to round up a number to.25..
So if i have 1.13 to return it 1.25

Monday, July 2, 2018 - 1:33:33 AM - ali Back To Top (76497)

hi there

in Example 1a  

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 

your comment is not true you mention it in Example 2c in right way. floor is for smallest integer and ceiling is for largest

 

Monday, September 11, 2017 - 3:02:37 PM - Kingston Xavier Back To Top (66183)

 

Why am I getting 1.1 as output for the below code, Ideally it should be 1.2

 DECLARE @value float

SET @value = 1.15

SELECT ROUND(@value, 1)  

 

Can you please guide me 

 

Monday, July 17, 2017 - 3:39:07 AM - Bilal Back To Top (59453)

Hello,

Thanks for the nice article. I have a question. I have a case:

Number is: 2.33 I want to return it 2 (Floor)

Number is: 2.54 I want to return it 3 (Ceiling)

 

How can I achieve this in TSQL given the ROUND, CEILING and FLOOR functions?

 

Thanks

Thursday, March 9, 2017 - 7:22:32 AM - Alex ritchie Back To Top (47658)

 Example 1e is wrong on the cieling command as well as the text.

 You say for 16.999999

"SELECT CEILING(@value) -- 16 - Smallest integer value

SELECT FLOOR(@value)     -- 16 - Largest integer value "

Where infact it should be:

"SELECT CEILING(@value) -- 17 - Largest integer value

SELECT FLOOR(@value)     -- 16 - Smallest integer value "

It did confuse me a little that one!

Friday, November 27, 2015 - 1:50:53 AM - sds Back To Top (39145)

declare @p1 int,@p2 int
exec ShowUsersByPage @CurrentPage=2,@PageSize=88,@TotalRecords=@p1 OUTPUT,@TotalPages=@p2 OUTPUT
select @p1,@p2


DROP PROCEDURE ShowUsersByPage
GO
CREATE PROCEDURE ShowUsersByPage
    @CurrentPage INTEGER,
    @PageSize INTEGER=0,     
    @TotalRecords decimal(10,2) OUTPUT,
    @TotalPages  decimal(10,2) OUTPUT   
AS  
    SET NOCOUNT ON
      
    DECLARE @FirstRecord INTEGER  
    DECLARE @LastRecord INTEGER  
    SET @FirstRecord = (@CurrentPage - 1) * @PageSize  
    SET @LastRecord = (@CurrentPage * @PageSize + 1)  

    DECLARE @temp TABLE  
    (  
        ClientId INTEGER IDENTITY PRIMARY KEY,
        UserId INTEGER,  
        UserName NVARCHAR(500),
        IsActive BIT,
        [CreatedDate] DATETIME,
        RoleID INTEGER,
        MobileNumber NVARCHAR(500),
        Phone NVARCHAR(500),
        Email NVARCHAR(100)
    )

    INSERT INTO @temp(UserId,UserName,IsActive,[CreatedDate],RoleID,MobileNumber,Phone,Email)  
        SELECT UserId,UserName,IsActive,[CreatedDate],RoleID,MobileNumber,Phone,Email  
            FROM Users        
            ORDER BY UserID ASC--DESC
    
        IF (@PageSize=0)
        BEGIN  
                SELECT UserId,UserName,IsActive,[CreatedDate],RoleID,MobileNumber,Phone,Email
                    FROM @temp  
        END ELSE  
        BEGIN  
            SELECT UserId,UserName,IsActive,[CreatedDate],RoleID,MobileNumber,Phone,Email
                    FROM @temp  
                    WHERE ClientId > @FirstRecord AND ClientId < @LastRecord  
        END
    
    
    SELECT @TotalRecords=COUNT(UserID) FROM @temp

    SELECT @TotalPages=CEILING(@TotalRecords/@PageSize)
    
    SET NOCOUNT OFF
-----------------------------

Monday, November 4, 2013 - 5:27:02 PM - Gadi Back To Top (27384)

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 Back To Top (26259)

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
PRINT ROUND(CAST(@value AS DECIMAL(10,4)), 2) 
172.77

Wednesday, June 12, 2013 - 1:56:53 PM - Dave Back To Top (25412)

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 Back To Top (25174)

Shri,

Can you post some sample data and the expected results?

Thank you,
Jeremy Kadlec
Community Co-Leader

Friday, May 24, 2013 - 2:22:50 AM - shri Back To Top (25115)

Hello everyone

How to get last digit to the left of decimal point in sql query?

 

Thursday, February 7, 2013 - 10:46:14 PM - Jeremy Kadlec Back To Top (22000)

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)
 
SET @value = 172.765
 
SELECT ROUND(@value, 2)
-- Result - 172.77

HTH.

Thank you,
Jeremy Kadlec
Community Co-Leader

Monday, February 4, 2013 - 11:45:05 AM - ClaudioRound Back To Top (21900)

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 Back To Top (21212)

Everyone,

This tip has been updated with additional examples and explanations.

Thank you,
Jeremy Kadlec

Monday, December 31, 2012 - 6:55:32 PM - Jeremy Kadlec Back To Top (21211)

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/select-command-for-sql-server/

HTH.

Thank you,
Jeremy Kadlec
Community Co-Leader

Thursday, November 15, 2012 - 1:07:48 AM - Sankar Back To Top (20349)

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 Back To Top (20348)

Thanks Jeremy for your response i tried that and found to be working..

Wednesday, November 14, 2012 - 8:43:43 AM - Jeremy Kadlec Back To Top (20335)

Sankar,

If I understand your question correctly, try this code:

declare @NumberToBeRounded numeric(29,3)
SET @NumberToBeRounded=182.354838456
-- select @NumberToBeRounded
SELECT ROUND(@NumberToBeRounded, 2)
 
-- Expected result: 182.360
 
-- getting output: 182.350 ( rounding to 0.01)

 

HTH.

Thank you,
Jeremy Kadlec

Wednesday, November 14, 2012 - 1:43:12 AM - Sankar Back To Top (20331)

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)
SET @NumberToBeRounded=182.354838456
select @NumberToBeRounded

Expected result: 182.360

getting output: 182.350 ( rounding to 0.01)

 

Thursday, October 18, 2012 - 5:10:21 PM - Sandeep Back To Top (19987)

Thank U.

Thursday, September 20, 2012 - 1:06:06 PM - Gene Wirchenko Back To Top (19595)
Integers can be rounded. round() will take a negative second argument. e.g. declare @SomeInt int=65536 select ROUND(65536,-3) // Result is 66000.














get free sql tips
agree to terms