round to nearest 10

Question

_{Thursday, July 28, 2011 3:35 AM}

i have a table which contains data like
2012
2300.4
3459
2301.6
4510.6
can i write a query which will round the data to the nearest 10 i.e.,
2012 - 2020
**2300.4 -2300
**3459 -3460
2301.6 - 2310
4510.6 -4510

All replies (8)

_{Thursday, July 28, 2011 5:36 AM ✅Answered}

This should do it:

select ceiling(floor(col1)/10)*10

_{Thursday, July 28, 2011 3:55 AM}

You seem to be always rounding upwards. If this is the case then a select like this will work:

select ceiling(col1/10)*10

If you want to round you would instead use something like this:

select cast(round(col1/10,0)*10 as int)

_{Thursday, July 28, 2011 4:04 AM}

you can use

• ceiling
• floor

Go through below blogs:

http://www.mssqltips.com/tip.asp?tip=1589

http://www.mathsisfun.com/sets/function-floor-ceiling.html

If this post helps you please mark as answer.

_{Thursday, July 28, 2011 4:14 AM}

frez i m not always rounding upwards in case of 2300.4 the result should be 2300  not 2310

_{Thursday, July 28, 2011 4:21 AM}

So what are the rules becuase you have rounded 2012 upwards, or is it random :)

_{Thursday, July 28, 2011 5:33 AM}

if value is like 21 - 29 new value wil be 30

if value is like 20.1, 20.220.9 new value will be 20 

_{Thursday, July 28, 2011 5:40 AM}

DECLARE @Test TABLE 
(
    Number FLOAT,
    RoundedNumber INT NULL
)

INSERT INTO @Test
SELECT 2012,NULL UNION ALL
SELECT 2300.4,NULL UNION ALL
SELECT 3459,NULL UNION ALL
SELECT 2301.6,NULL UNION ALL
SELECT 4510.6,NULL 

UPDATE  t
SET t.RoundedNumber = t.Number + CASE CONVERT(INT,t.Number)%10 WHEN 0 THEN 0 ELSE (10-(CONVERT(INT,t.Number)%10))  END
FROM @Test t

SELECT * FROM @test

_{Thursday, July 28, 2011 5:41 AM}

thankx frez .. it worked for me..