3

it s enough to round a column value in plsql by using round(value,2). For example 1.2222 can be converted to 1.22 by that function in plsql.

How can I do that in SQL Server? When I use round(1.2222,2), it converts 1.2200. I want 1.22. Thanks for help

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
jhash
  • 749
  • 3
  • 9
  • 19

2 Answers2

5

Mathematically, 1.2200 and 1.22 are the exact same number.

The display of the number is actually a string that is different from the physical content of the number, so the number is rounded the same in SQL Server and Oracle, it's just displayed diferently.

More, about SQL float point types: if you declare a variable as having a s decimal places, it will always display as having s decimal places, regardless of the value, for example

declare @x decimal(10,6)
declare @y decimal(10,2)
set @x =3
set @y=@x -- identical to set @y=cast(@x as decimal(10,2))

select @x -- selects 3.000000
select @y -- selects 3.00

select 'The number is ' + cast(@x as varchar) -- selects 'The number is 3.000000'
select 'The number is ' + cast(@y as varchar) -- selects 'The number is 3.00'

However, there is a technical difference about the meaning of 3.00 and 3.0000, and that's the relative error of the number. Since all real numbers are rounded, 3.00 actually means 3 +/- 0.005 , and 3.0000 is actualy 3 +/- 0.00005, as more significant digits are known.

SWeko
  • 30,434
  • 10
  • 71
  • 106
  • so how can change its displaying style?I dont wanna see these extra zeros in my reports – jhash Mar 31 '11 at 08:28
  • @jhash: `cast(@x as decimal(10,2))` -- this will only output 2 decimal places. Maybe it would help if you told us exactly which format you want (e.g. always two decimal places, or as many decimal places as needed, ...). – Heinzi Mar 31 '11 at 08:33
  • @Heinzi,thanks for solution,it works.I just wondered that why round doesnt do the same thing in mssql although 3.0000 or 3.00 are the same. – jhash Mar 31 '11 at 08:50
  • 2
    @jhash, the problem is the type of the column, not the value. `Cast` changes the type, `round` changes the value. – SWeko Mar 31 '11 at 08:51
0

decimal(10,4) and decimal(10,2) are two different types. so 25.55 and 25.5555 are different types. but if you use float(4), then those two numbers are same data types.

Davor Mlinaric
  • 1,989
  • 1
  • 19
  • 26