From: Don on 24 Mar 2010 18:17 How do you always round up? I want to to store a value to the 4th decimal place but don't want to round down select round(.333888888,4) -- Want to return .3339 select round(.333811111,4) -- Want to return .3339 not .3338 Thanks From: Erland Sommarskog on 24 Mar 2010 18:59 Don (Don(a)discussions.microsoft.com) writes:> How do you always round up? I want to to store a value to the 4th decimal > place but don't want to round down > > select round(.333888888,4) -- Want to return .3339 > select round(.333811111,4) -- Want to return .3339 not .3338 Maybe: select round(.333811111,4, 1) + 0.0001 The third parameter to round when non-zero specifies that the number is to be truncated. Then add 0.0001 to that. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx From: Mark Fitzgerald on 25 Mar 2010 04:15 1) The ceiling function rounds to integer above the number. Use the POWER function to shift the number so it is an integer and then move it back. declare @sig int set @sig = 4 select CEILING(.333888888 * POWER(10,@sig))/POWER(10,@sig) select CEILING(.333811111 * POWER(10,@sig))/POWER(10,@sig) go 2) Add a half to the least significant value. If you were dealing with integers then 0.5 but if you want 5th decimal add 0.00005. This will force the rounding up for values in the x.00001 to x.00049 range but leave the x.00050 to x.00099 unaffected. declare @addon decimal(6,5) set @addon = 0.00005 select ROUND(.333888888 + @addon,4) select ROUND(.333811111 + @addon,4) Fitz "Don" wrote in message news:CBDBFAC5-A1C6-4983-A6BF-91FF1D28333D(a)microsoft.com...> How do you always round up? I want to to store a value to the 4th decimal > place but don't want to round down > > select round(.333888888,4) -- Want to return .3339 > select round(.333811111,4) -- Want to return .3339 not .3338 > > Thanks From: Andrew Morton on 25 Mar 2010 06:49 Don wrote:> How do you always round up? I want to to store a value to the 4th > decimal place but don't want to round down > > select round(.333888888,4) -- Want to return .3339 > select round(.333811111,4) -- Want to return .3339 not .3338 What about negative values? Do you want to round up or away from zero? Andrew  |