Weirdness in SQL numeric handling

Question

Post reply

Weirdness in SQL numeric handling

Coyote Blue

SSC-Addicted

Points: 486
More actions
November 20, 2008 at 3:04 pm

#194213

(Please forgive me in advance if this is not in a proper post format, this is my first time posting a new topic.)
I am trying to get a resultant number with 4 decimal points of precision.
when using the sql below, I am getting odd results, see lower down...
What am I missing? I am expecting my last numbers to come out as 123.1234 (or at least 123.123x); but it appears to be returning incorrect values... I have tried various datatypes for @res, all to no avail...
What Am I Missing/ What am I doing Wrong?
Thanks in Advance -
CoyoteBlue
CODE SAMPLE:
declare @res as float
select @res = 123.123456789
select @res
select CAST(@res as float(4))
select CONVERT(FLOAT, @res, 1)
select ROUND(@res, 4)
select CONVERT(varchar, ROUND(@res, 4)) + ' pt'
select cast(ROUND(@res, 4) as varchar) + ' pt'
RESULTS:
----------------------
123.123456789
-------------
123.1235
----------------------
123.123456789
----------------------
123.1235
---------------------------------
123.124 pt
---------------------------------
123.124 pt

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

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

Bevan Keighley SSCommitted Points: 1825 More actions · Answer 1

Hi,

Is this what you are after?

select cast(@res as decimal(20,4))

Coyote Blue SSC-Addicted Points: 486 More actions · Answer 2

Closer to what I was expecting; but the rounding still seems *wrong* to me (and I am an 'untrained' dba picking this up as I go along....)

does it round as

123.12345 (drop the rest, round 4 off of 5...)

123.1234 five is over the midpoint so 4 rounds UP,

and the @res value = 123.1235

Bevan Keighley SSCommitted Points: 1825 More actions · Answer 3

I think that rounding is correct.

Try this for example:

declare @res1 as float, @res2 as float

select @res1 = 123.123456789

select @res2 = 123.123444444

select cast(@res1 as decimal(20,4))

select cast(@res2 as decimal(20,4))

Coyote Blue SSC-Addicted Points: 486 More actions · Answer 4

Coyote Blue (11/20/2008)
Closer to what I was expecting; but the rounding still seems *wrong* to me (and I am an 'untrained' dba picking this up as I go along....)
does it round as
123.12345 (drop the rest, round 4 off of 5...)
123.1234 five is over the midpoint so 4 rounds UP,
and the @res value = 123.1235

oh, AND I need to be able to add the '+pt' to the result.... :ermm:

Bevan Keighley SSCommitted Points: 1825 More actions · Answer 5

Cast it as a string (after casting as decimal) and then you can add the strings together:

select cast(cast(@res1 as decimal(20,4)) as varchar(30)) + 'pt'

Coyote Blue SSC-Addicted Points: 486 More actions · Answer 6

thanks B!

(all this casting for a sql solution? I feel like a fisherman! haha I slay me....):crazy:

Bevan Keighley SSCommitted Points: 1825 More actions · Answer 7

Hey glad to help! There isn't much activity on the forums when I am logged in so it is great to help out where I can. I think everyone in the US or Europe are still in bed!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

Haven't gone to bed yet, just finished dinner. It is only 7:42 PM where I live in the USA.