Round to nearest 5 in SQL Server

sql round up to nearest whole number
sql round down to 2 decimal places
sql round to nearest 10
sql 2 decimal places without rounding
sql server round
sql ceiling to 2 decimal places
sql round not working
sql always round up

I have a Money column in my SQL Server 2008 table. In my below query how can I round it to nearest 5$

select FineAmount from tickets

Thanks

select round(FineAmount*2,-1)/2 from tickets

or to put nicholaides suggestion in sql

select round(FineAmount/5,0)*5 from tickets

The example assumes that FineAmount is of type money. The second approach is probably better as the first one works with the limit of maximum_value_of_money_type/2

More on ROUND

ROUND (Transact-SQL), Results in an arithmetic overflow, because 748.58 defaults to decimal(5,2), which cannot return 1000.00. To round up to 4 digits, change the data  ROUND (Transact-SQL) 12/14/2017; 2 minutes to read +4; In this article. APPLIES TO: SQL Server Azure SQL Database Azure Synapse Analytics (SQL DW) Parallel Data Warehouse Returns a numeric value, rounded to the specified length or precision.

A general math solution:

Divide by 5, round to the nearest integer, then multiply by 5.

SQL Server Rounding Functions, SQL Server ROUND, CEILING and FLOOR Examples for Integer Data Types The 5 in the second digit to the right of the decimal point is significant when When rounding to the nearest 100 (or 1,000), 6 is just closer to zero. SQL Server ROUND() Function SQL Server Functions. Example. Round the number to 2 decimal places: SQL Server (starting with 2008), Azure SQL Database, Azure SQL

If you want to truncate (round-down) to a grouping of 5 use the modulo function; in Microsoft SQL Server this is %

ie: field1 - (field1 % 5)

If you had field1 == 3, then the calc would be:

3 - (3 % 5) = 0

if it were 13:

13 - (13 % 5) = 10

Simply add 5 if you want to round up

See also 'MOD' is not a recognized built-in function name

Rounding a number up to the nearest 5 – SQLServerCentral, Rounding a number up to the nearest 5 – Learn more on the So I am attempting to mimic some legacy code on an old server at my work. It has a Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/ SQL – rounding to nearest 5. Posted on March 19, 2013October 20, 2014by Kev Riley. Had a requirement to round some price values to the nearest £5 in a SQL stored proc. It’s easy to round to the nearest 10 using a negative value for the length parameter of the ROUND function i.e. select round(12, -1)

DECLARE @Amount DECIMAL(18,3) ; SET @Amount = 7818.32

SELECT(Round((@Amount-CAST(@Amount AS INT))*100 /5,0)*5 /100) 
      + CAST( @Amount AS INT)

-- you'll get 7818.30

Round Up/Down to the Nearest 5, 10, 100 in SQL, In an application you need to round up numbers to nearest 5 or nearest 10 all the time. For example, you might need to display 1.44 as 1.45 and 1.89 as 1.90. This can be done at either the coding end or at the Database end itself. Here is a small function that you can add in your database to achieve this result. In SQL Server, the T-SQL ROUND() function allows you to round a number to a specified length or precision. You provide the number as an argument, as well as the length you’d like the number to be rounded to.

My first solution was

create function dbo.udf_RoundToNearest(@x int, @to int)
returns int
with schemabinding as begin
return @to * convert(int, round(convert(float, @x) / convert(float, @to), 0))
end

This works, but is considered by MSSQL as 'imprecise' since it uses floating point numbers internally. That stops it being used in indexed views. You can instead do the job only with integer arithmetic:

create function dbo.udf_RoundToNearest(@x int, @to int)
returns int
with schemabinding as begin

declare @m int
set @m = abs(@x) % abs(@to)

declare @trunc int
set @trunc = abs(@x) - @m

declare @r int
set @r = case when @m * 2 >= abs(@to) then @trunc + abs(@to) else @trunc end

return case when @x < 0 then -@r else @r end

end

How to Round Up or Round Down Number in SQL Server , Another interesting function which was introduced in SQL Server 2016 was I want to round down to nearest to 5 multiples E.g. 20.00 to 24.99  1.0 -> 5 (everything less than 5 gets rounded to give) 5-> 5. 5.75->5. 8->10. 11->15. 86.5->90. so any number that has a 5 in it before the decimal - will stay rounded to 5. Example - 75.65 -> 75

SQL Server ROUND() Function, The ROUND() function rounds a number to a specified number of decimal places. Works in: SQL Server (starting with 2008), Azure SQL Database, Azure SQL  I want to round down to nearest to 5 multiples E.g. 20.00 to 24.99 would display 20 and 25.00 to 29.99 would display 25 He has authored 12 SQL Server database

Rounding off to Nearest 5, 10, 50 - SQL, I have a requirement to round off figures by: nearest 5 if less than 100, nearest I understand that SQL Server 2012 has the equivalent of Excel  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.

Overview of SQL Server Rounding Functions – SQL Round, Ceiling , This article explores SQL Server rounding functions (Round,Ceiling and like SQL Round, Ceiling and Floor to round the values to the nearest numbers. Example 5: SQL Server rounding function Truncation using the third 

Comments
  • Can you define "nearest"? I.e., what result do you want for values of 0, 1, 2.5, and 4 go? Rounding money can mean different things depending on the use case.
  • The only note I'd add to this is make sure that it divides the 5 as a decimal, not an integer. you can either wrap it in a convert/cast, or add a 0 to it, to make sure it works correctly. e.g. select round(FineAmount/5.0,0)*5 from tickets
  • Note that "Operand data type float is invalid for modulo operator"
  • But 10 rounding up is 10-(10%5)+5 = 15 but should be 10.
  • The integer value in the ROUND overload is to indicate decimal precision, not multiple to round by.