Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AAAV
Posting Yak Master
152 Posts |
 Posted - 2014-04-29 : 10:59:44
|
| I am trying to round a value to 0 SELECT ROUND(dbo.VIEWA.yr4_20k, 1) AS val1, ROUND(dbo.VIEWA.yr4_20k, 0) AS val2 FROM dbo.temptableit returns 449.5 and 449 respectively. I was expecting 450 for the second value. The column used is of datatype float. The same view was working good in sql 2008 did anything change in sql 2012 related to data types or rounding functionality?How does the round function work behind the screens?Thanks |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2014-04-29 : 11:24:24
|
I am not aware of any changes in the behavior of round function in SQL 2012 when compared to previous versions. The result you are getting are not necessarily wrong. It depends on the data. The following example demonstrates how rounding to one decimal place results in 449.5 and zero decimal places results in 449.DECLARE @x FLOAT = 449.46;SELECT ROUND(@x,1),ROUND(@x,0); |
 |
|
|
AAAV
Posting Yak Master
152 Posts |
Posted - 2014-04-29 : 11:39:15
|
| The value is exactly 449.5 not anything less than that... but it comes off a view and the value is a float. thought it gives 450 in your example but from the view above it gives 449... i am not sure what i am missing to see... |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2014-04-29 : 12:07:59
|
Floating point is an approximate representation. So what seems like 449.5 may not really be 449.5. It could be slightly more, slightly less. In the example below, value of @x which seems like 449.5 is in reality slightly less than 449.5DECLARE @x FLOAT = 44949999.99999996/100000;SELECT @x; -- Prints out 449.5SELECT ROUND(@x,1),ROUND(@x,0); -- prints out 449.5 and 449 Float and real are approximate numeric data types, so this is an expected and correct behavior. If that is an issue, you should use exact numeric data types such as DECIMAL. See here: http://msdn.microsoft.com/en-us/library/ms187752.aspx |
|
|
|
|
|
|
|
|
Rounding