Round(9.x,0) Error where x >=5 and x <= 9)

Question

Round(9.x,0) Error where x >=5 and x <= 9)

arvind_rn

SSC Rookie

Points: 32
More actions
May 4, 2015 at 2:49 am

#307708

The following statement
select ROUND(9.5, 0)
gives error:
Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type numeric.
at SQLExpress 2005 and SQLExpress2014 versions
the same error for any number between 9.5 to 9.9999

Viewing 4 posts - 1 through 4 (of 4 total)

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

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

Weird. I suspect because Round returns numeric of the same precision and scale as its input, and 10 won't fit into a numeric (2,1)

As a workaround:

select ROUND(CAST(9.5 AS Numeric(3,1)), 0)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

arvind_rn SSC Rookie Points: 32 More actions · Answer 2

Thanks for the workaround :-),

yes the same error for 99.5 or 999.5.

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 3

https://connect.microsoft.com/SQLServer/feedback/details/791155/round-function-returns-arithmetic-overflow-for-literal-values-0-5-and-1

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day