Prev: DBTYP.NET Studio 2010 - Fastest Database Comparison Suite Released
Next: function or View
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" <Don(a)discussions.microsoft.com> 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


 | 
Pages: 1
Prev: DBTYP.NET Studio 2010 - Fastest Database Comparison Suite Released
Next: function or View