Q: T-SQL - how to round DOWN to nearest .05 The database I am using is SQL Server 2005. I am trying to round values DOWN to the nearest .05 (nickel).
So far I have:
SELECT ROUND(numberToBeRounded / 5, 2) * 5
which almost works - what I need is for the expression, when numberToBeRounded is 1.99, to evaluate to 1.95, not 2.

A: Specify a non-zero value for a third parameter to truncate instead of round:
SELECT ROUND(numberToBeRounded / 5, 2, 1) * 5

Note: Truncating rounds toward zero, rather than down, but that only makes a difference if you have negative values. To round down even for negative values you can use the floor function, but then you can't specify number of decimals so you need to multiply instead of dividing:
SELECT FLOOR(numberToBeRounded * 20) / 20


A: If your data type is numeric (ISO decimal) or `money, you can round towards zero quite easily, to any particular "unit", thus:
declare @value money = 123.3499
declare @unit  money = 0.05

select value                = value ,
       rounded_towards_zero = value - ( value % @unit )
from #foo

And it works regardless of the sign of the value itself, though the unit to which you're rounding should be positive.
 123.3499 ->  123.3000
-123.3499 -> -123.3000

