# Rounding an integer number to lowest nearest number

### Question

• Hi,

I want to round off an integer number to its lowest nearest number.

For example : 299 as 200.

I can make 299 to 300 by using round function. but how can i achieve 299 as 200.

Kindly help.

Tuesday, October 08, 2013 6:58 AM

### All replies

• Hi

you can change the 100 to 1000 if you want 3 dight round off...

```declare @value int = 299,
@round int = 100
select case when @value > @round then (@value) - @value%@round else @value end```

Thanks

Saravana Kumar C

• Edited by Tuesday, October 08, 2013 7:09 AM
Tuesday, October 08, 2013 7:06 AM
• Hello,

With a little math: Substract the module 100 result from your value:

```;with test as
(SELECT 299 AS Val
UNION ALL
SELECT 249
UNION ALL
SELECT 201
UNION ALL
SELECT 300)

SELECT Val
,Val - (Val % 100) AS NextLowest
FROM test```

Olaf Helper

[ Blog] [ Xing] [ MVP]

Tuesday, October 08, 2013 7:15 AM
• select (299 /100)*100

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

Tuesday, October 08, 2013 7:17 AM
• ```
SELECT LEFT(298,1) * POWER(10,LEN(298)-1)
, LEFT(2980,1) * POWER(10,LEN(2980)-1)
/*200  2000*/```

Tuesday, October 08, 2013 1:51 PM
• You can do that with the round function by using the third parameter of the round function to force it to truncate (round down).

```declare @value int = 299
select Round(@value, -2, 1)```

Tom

Tuesday, October 08, 2013 1:53 PM