Friday, August 03, 2007

SQL Round To Quarter

This is a reminder to myself how to round to the nearest fraction, in this case a quarter.
Next time I won't spend 15 minutes trying to recall the rounding formula. I'll just look it up here.

DECLARE @target smallmoney
SET @target = 597.80

--round to quarter hour
SELECT ROUND( @Target/25.0, 2) * 25

-- Result = 597.75


Jeff said...

I ran across your blog and enjoyed reading some of it and catching up with your family a bit. If you like, take a look at mine and say hi.

Levi Watts said...

Found your article via Google, but needed to round up instead of regular rounding. Since the CEILING function doesn't have two inputs, I needed to change it to this:
SELECT CEILING(1.26 * 4) / 4 -- 1.50

Thank you

Monday's Child said...

OMG Thank you! I have to program to round to the nearest 1/16th and I couldn't figure it out.