SET @FirstRecord = (@CurrentPage – 1) * @PageSize
ThanksJeremy for your response i tried that and found to be working..
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.
Daylight Savings Time Functions in SQL Server
Negative number rounds on the left side of the decimal point
Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an co-founder and Edgewood Solutions SQL Server Consultant.
SET @NumberToBeRounded=182.354838456
If I understand your question correctly, try this code:
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
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
INSERT INTO @temp(UserId,UserName,IsActive,[CreatedDate],RoleID,MobileNumber,Phone,Email)
Sorry for my delayed response. Do you just need to write a SELECT or UPDATE statement? What are you trying to do?
— getting output: 182.350 ( rounding to 0.01)
SELECT UserId,UserName,IsActive,[CreatedDate],RoleID,MobileNumber,Phone,Email
SQL Server Rounding Functions – Round, Ceiling and Floor
Technically, there arent 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.
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:
Truncation of the value to round occurs when this value is not 0 or not included
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.
SQL Server User Defined Function Tips
Example 1c- Lets 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.
Forgotten SQL Server Functions – VARP, SOUNDEX and…
How to get last digit to the left of decimal point in sql query?
SQL Server T-SQL Aggregate Functions
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 1a- In this first example lets 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.
The moral of the story is that if you really care about exact fractional values then dont use FLOAT or REAL. Even casting it to DECIMAL before ROUNDING may help.
declare @NumberToBeRounded numeric(29,3)
How can I achieve this in TSQL given the ROUND, CEILING and FLOOR functions?
In answer to ClaudioRounds question why this rounding does not work (for 172.765).
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:
This data type can be an int (tiny, small, big), decimal, numeric, money or smallmoney
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
In Numerical Control programming this is done all the time by using scaling and scaling factors. The scaling factor in this case is 10.
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, lets see how the values are evaluated.
ROUND – Rounds a positive or negative value to a specific length and accepts three values:
ClientId INTEGER IDENTITY PRIMARY KEY,
Check out these related tips on MSSQLTips:
declare @NumberToBeRounded numeric(29,9)
This tip has been updated with additional examples and explanations.
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.
Post a comment or let the author know this tip helped.
I saw your recent tip onCalculating Mathematical Values in SQL Serverand 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 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.
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
Some names and products listed are the registered trademarks of their respective owners.
Date/Time Conversions Using SQL Server
Number is: 2.54 I want to return it 3 (Ceiling)
SET @LastRecord = (@CurrentPage * @PageSize + 1)
Another fun fact is that ROUND(CAST(172.0099 AS FLOAT), 2) returns 172.76, but if you add a trailing 0 then ROUND(CAST(172.00990 AS FLOAT), 2) returns 172.77. Dont ask me why.
All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).
Integers can be rounded. round() will take a negative second argument. e.g. declare @SomeInt int=65536 select ROUND(65536,-3) // Result is 66000.
Example 1d- Lets round a negative integer and see the impacts with the result commented out on the right of the function.
From a definition perspective, lets start here:
Example 1b- Since the CEILING AND FLOOR functions do not have any optional values, lets test some options with the ROUND function. In this example, lets 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.
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.
Subtracting 128 from this value drops the two leftmost bits, so it gains two more fractional bits resulting in 101100.10. (The mantissa is always 53 bits long in a float.) This is about44.57, so even though it has the same fractional digits the value of ROUND(44.765, 2) is 44.77.
SELECT @TotalRecords=COUNT(UserID) FROM @temp
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
We are using numeric datatype size as 29,9 In this I am facing problem in rounding off any body can help
SQL Server 2012 Functions – First_Value and Last_V…
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.
SELECT UserId,UserName,IsActive,[CreatedDate],RoleID,MobileNumber,Phone,Email
It did confuse me a little that one!
SQL Server Rounding Functions – Round, Ceiling and…
WHERE ClientId @FirstRecord AND ClientId @LastRecord
Thanks for the nice article. I have a question. I have a case:
Can you post some sample data and the expected results?
SET @NumberToBeRounded=182.354838456
SQL Server 2012 Functions – Lead and Lag…
SQL Server ROUND, CEILING and FLOOR Examples for Decimal, Numeric and Float Data Types
Example 1eis wrong on the cieling command as well as the text.
SELECT CEILING(@value) — 17 – Largestinteger value
Why am I getting 1.1 as output for the below code, Ideally it should be 1.2
exec ShowUsersByPage @CurrentPage=2,@PageSize=88,@[emailprotected]OUTPUT,@[emailprotected]OUTPUT
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
Copyright (c) 2006-2018Edgewood Solutions, LLCAll rights reserved
Positive number rounds on the right side of the decimal point
SELECT CEILING(@value) — 16 – Smallest integer value
SELECT UserId,UserName,IsActive,[CreatedDate],RoleID,MobileNumber,Phone,Email
Rounding values can cause confusion with various data types, so be sure to understand the user requirements then translate them to the correct data types and rounding functions.
FLOAT and REAL data types are approximate values. I would move to a decimal data type based on the example you provided:
SELECT @TotalPages=CEILING(@TotalRecords/@PageSize)
Number is: 2.33 I want to return it 2 (Floor)
Instead of using cursors is there any other medthod is available. becoz cursor taking more time to fecth records when huge data
*** NOTE *** – If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
getting output: 182.350 ( rounding to 0.01)
The code DECLARE @value FLOAT = 172.765 stores the binary number 10101100., which is about 172.. 172.76 is the correct rounded value for something less than 172.765.
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
Thursday, September 20, 2012 – 1:06:06 PM – Gene Wirchenko
ALERT: Did you know 66% of DBAs say their workload is increasing! – Click here to learn more.
Lets walk through each function with a few different data types to see the results.
Calculating Mathematical Values in SQL Server