SQLServerCentral / SQL Server 2008 / SQL Server Newbies / Rounding Up with given precision / Latest PostsInstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 18 Apr 2014 22:01:23 GMT20RE: Rounding Up with given precisionhttp://www.sqlservercentral.com/Forums/Topic1449628-1292-1.aspxHere is a solution using the ROUND function:[code="sql"]with test_data_cte as(select datavaluefrom ( values (5.0000016), -- should be 5.01 (6.1000138), -- should be 6.11 (7.1200073), -- should be 7.13 (5.0000000), -- should be 5.00 (6.1000000), -- should be 6.10 (7.1200000) -- should be 7.12 ) dt (datavalue))select datavalue, rounded_datavalue = convert(numeric(8,2),round(datavalue+0.0049999,2))from test_data_cte[/code]Results:[code="plain"]datavalue rounded_datavalue--------------------------------------- -----------5.0000016 5.016.1000138 6.117.1200073 7.135.0000000 5.006.1000000 6.107.1200000 7.12[/code]Tue, 07 May 2013 10:28:14 GMTMichael Valentine JonesRE: Rounding Up with given precisionhttp://www.sqlservercentral.com/Forums/Topic1449628-1292-1.aspxI came up with another solution based on the same mathematical concept as Lynn's, but I think Lynn's is more elegant:[code="sql"]with TestData as (select datavaluefrom (values(5.0000016), -- should be 5.01(6.1000138), -- should be 6.11(7.1200073), -- should be 7.13(5.0000000), -- should be 5.00(6.1000000), -- should be 6.10(7.1200000) -- should be 7.12)dt(datavalue))select datavalue, (ceiling(datavalue * 100) * 1.0) / 100, -- Lynn's idea CASE WHEN (datavalue * 100) % 1 > 0 THEN ROUND(datavalue, 2, 1) + 0.01 ELSE ROUND(datavalue, 2, 1) END -- My ideafrom TestData;[/code]Tue, 07 May 2013 09:04:12 GMTwolfkilljRE: Rounding Up with given precisionhttp://www.sqlservercentral.com/Forums/Topic1449628-1292-1.aspxHere is another option (The CTE is just how I passed in the sample data):[code="sql"]with TestData as (select datavaluefrom (values(5.0000016), -- should be 5.01(6.1000138), -- should be 6.11(7.1200073), -- should be 7.13(5.0000000), -- should be 5.00(6.1000000), -- should be 6.10(7.1200000) -- should be 7.12)dt(datavalue))select datavalue, (ceiling(datavalue * 100) * 1.0) / 100from TestData;[/code]Mon, 06 May 2013 11:10:33 GMTLynn PettisRE: Rounding Up with given precisionhttp://www.sqlservercentral.com/Forums/Topic1449628-1292-1.aspx[quote][b]Jeff Moden (5/6/2013)[/b][hr]Haven't proofed (I normally don't do rounding) it but why wouldn't ROUND(somenumber+.00499999,2,1) work for you?[/quote]This would work as long as it is guaranteed that that each number will have a digit greater then zero in one of the 2 most left digits after the period (Hope that I wrote it correctly in English:-)). Here is an example:[code]--with both numbers the user wanted to get 0.01select ROUND(0.0100000+.00499999,2,1)select ROUND(0.0000001+.00499999,2,1) [/code]AdiMon, 06 May 2013 05:39:58 GMTAdi Cohn-120898RE: Rounding Up with given precisionhttp://www.sqlservercentral.com/Forums/Topic1449628-1292-1.aspxHaven't proofed (I normally don't do rounding) it but why wouldn't ROUND(somenumber+.00499999,2,1) work for you?Mon, 06 May 2013 05:25:03 GMTJeff ModenRE: Rounding Up with given precisionhttp://www.sqlservercentral.com/Forums/Topic1449628-1292-1.aspxIt works for me. Thank you very much.Mon, 06 May 2013 03:39:01 GMTphoenix_RE: Rounding Up with given precisionhttp://www.sqlservercentral.com/Forums/Topic1449628-1292-1.aspxMaybe there is a better way, but here is one way:[code]declare @Num numeric(8,7)set @Num = 0.0000001SET @Num = CASE WHEN CAST(@Num AS NUMERIC(3,2)) = @Num THEN @Num ELSE CAST(@Num AS NUMERIC(3,2)) + 0.01 ENDSELECT @Num[/code]AdiMon, 06 May 2013 03:32:22 GMTAdi Cohn-120898Rounding Up with given precisionhttp://www.sqlservercentral.com/Forums/Topic1449628-1292-1.aspxI would like to round up some figures with given precision. So far I can't find a way to do it. ROUND function doesn't work for me because it can't round up, CEILING doesn't work as well because the results is integer.What I would like to achieve is a rounding up with give precision of 2 places after dot i.e.5.0000016 should be 5.016.1000138 should be 6.117.1200073 should be 7.13Is it any way to do it in SQL?Mon, 06 May 2013 03:20:32 GMTphoenix_