0

I have a sample input of 1.0950 with a float type.

Doing this for positive numbers is correct, with a 1.1 result

declare @x float
set @x = 1.0950
select round(@x, 1)

But when I change @x to negative -1.0950 it still rounds to -1.1 which is incorrect it should always round away from zero, so the answer should be -1.09

I would rather not do two statements, one for positive and one for negative. If anyone could assist I would be very grateful!

Miki
  • 40,887
  • 13
  • 123
  • 202
Jjj
  • 155
  • 1
  • 13
  • 1
    If it should always round *away* from zero then surely `-1.1` is the right answer, as it is further from zero than `-1.09` ? –  Aug 20 '15 at 10:38
  • Sorry I forgot to put additional information. When negative -1.0950 it should round down to -1.09 but it's rounding up as it has .5. According to the ROUND documentation if positive it rounds up if negative it rounds down. This isn't happening for me. – Jjj Aug 20 '15 at 10:44
  • possible duplicate of [Why does Math.Round(2.5) return 2 instead of 3 in C#?](http://stackoverflow.com/questions/977796/why-does-math-round2-5-return-2-instead-of-3-in-c) – Helio Aug 20 '15 at 10:52
  • Actually, that *is* rounding down - it may seem counter-intuitive, but `-1.1` is less than `-1.09`. –  Aug 20 '15 at 11:17

3 Answers3

0
declare @x float
set @x = -1.0950
select round(@x, 2)
Helio
  • 621
  • 1
  • 4
  • 24
  • That is correct for negative numbers. Then when the number is positive the result is 1.09, which is incorrect – Jjj Aug 20 '15 at 10:47
  • 1.09 is correct when you round with 2 for positive number – Helio Aug 20 '15 at 10:49
  • Being positive it should be 1.1, when negative it should be -1.09. This makes sense when you're doing invoicing. I can do it with two statements but was hoping for one. Thanks though – Jjj Aug 20 '15 at 10:53
0

try this way

declare @x float
set @x = -1.0950
select case when @x>0 then round(@x, 1)else ROUND(@x,2)end
Mukesh Kalgude
  • 4,814
  • 2
  • 17
  • 32
0

Try this syntax instead, it will add .005 and cast as decimal(18,2) getting the needed result:

SELECT x,cast(x + .005 as decimal(18,2)) val
FROM 
  (VALUES(1.0950),(-1.0950)) x(x)

Result:

      x     val
 1.0950    1.10
-1.0950   -1.09
t-clausen.dk
  • 43,517
  • 12
  • 59
  • 92