Numbers - getting the right decimal places

Question

Post reply

Numbers - getting the right decimal places

olibbhq

SSCarpal Tunnel

Points: 4034
More actions
July 20, 2010 at 4:27 am

#226973

Hi,
A query more than anything I am sure you must have come across before:
I have a number set as float - say 10.868
I would like to round this number to two decimal places getting 10.87
however is there are other depths say 10.8
but I want to return this value as 10.8 not 10.80
Any ideas as to how this would be achieved I have been playing around with cast and round but cant seem to get it right.
Cheers,
Oliver

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

Try this and let us know if this worked for you!

NUMERIC (A , 1) ?

olibbhq SSCarpal Tunnel Points: 4034 More actions · Answer 2

Hi,

Thanks for the reply using numeric

SELECT CAST(10.086 AS NUMERIC(20 , 2))

SELECT CAST (10.8 AS NUMERIC(20 , 2))

returns

10.09

and 10.80 - but I would like it to report 10.8 because otherwise its reporting an accuracy value higher than I actually have.

Thanks,

Oliver

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 3

oliver.morris (7/20/2010)
Hi,
Thanks for the reply using numeric
SELECT CAST(10.086 AS NUMERIC(20 , 2))
SELECT CAST (10.8 AS NUMERIC(20 , 2))
returns
10.09
and 10.80 - but I would like it to report 10.8 because otherwise its reporting an accuracy value higher than I actually have.
Thanks,
Oliver

Can you try :

SELECT CAST(10.086 AS NUMERIC(20 , 1))

SELECT CAST (10.8 AS NUMERIC(20 , 1)) ??

olibbhq SSCarpal Tunnel Points: 4034 More actions · Answer 4

Sorry, thats what I did try. Did you mean to change the two lines.

Many Thanks,

Oliver

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 5

oliver.morris (7/20/2010)
Sorry, thats what I did try. Did you mean to change the two lines.
Many Thanks,
Oliver

Yes oliver, changed from

SELECT CAST(10.086 AS NUMERIC(20 , 2 [/i] ))

SELECT CAST (10.8 AS NUMERIC(20 , 2 [/i]))

to

SELECT CAST(10.086 AS NUMERIC(20 , 1 [/i] ))

SELECT CAST (10.8 AS NUMERIC(20 , 1 [/i]))

Please try this second one, and let us know..Basically the changed the precision to 1..

olibbhq SSCarpal Tunnel Points: 4034 More actions · Answer 6

Sorry,

when I run these I get

10.1

and

10.8

when really I would like

10.08

and

10.8

Many Thanks for your help,

Oliver

olibbhq SSCarpal Tunnel Points: 4034 More actions · Answer 7

Found some things and put them together to get this,

work in progress but promising

DECLARE @TestNum float

SET @TestNum = 99.001 --0.123456780000000 --99.1 --90

select Case when (CHARINDEX('.',REVERSE(@TestNum))-PATINDEX('%[^0]%',REVERSE(@TestNum))) > 2 then cast(@TestNum as decimal(20,2)) else CAST(@TestNum as decimal(20,1)) end

Cheers

Oliver