January 5, 2006 at 9:15 pm
I noticed an interesting error illustrated in the queries below:
-- 1. varchar number less than 10 with real divisor --> works
select '9'/3.785
-- 2. varchar number 10 with integer divisor --> works
select '10'/3
-- 3. integer number 10 with real divisor --> works
select 10/3.785
-- 4. varchar number 10 using the multiplication operator with real operand --> works
select '10' * 1/3.785
-- 5. varchar number 10 with real divisor --> ERROR
select '10'/3.785
This isn't a critical issue (it can be easily worked around by casting the varchar to a numeric), I'm just curious if anyone has any ideas as to why the last query generates an error when the others do not.
January 6, 2006 at 4:54 am
When SQL Server converts automatically, it sets the resulting type to match the rest of the statement. Error message returned is "Arithmetic overflow error converting numeric to data type numeric." You are dividing by 3.785; NUMERIC data type defines how many digits there are in total and how many decimal places. So, 3.785 is supposed by SQLS to be NUMERIC(4,3)... so it tries to convert '10' to NUMERIC(4,3), and that is why you are getting that error. There is only one place to the left of the dot in this format, no room for two digits of the number 10. Try this:
select CAST(10 as numeric(4,3))
select '10'/CAST(3.785 as numeric(4,3))
select '10'/CAST(3.785 as numeric(5,3))
As you see, the first two statements return the same error as your SQL, while the last runs OK. That seems to show plainly what happens.
Generally speaking, you should never rely on implicit conversion, because you never know what the result will be. In fact, I believe that not defining data types and conversions properly is one of worst practices, because it is a nightmare to find in such code why this statement is failing suddenly, after long time without problems.
HTH, Vladan
January 9, 2006 at 3:17 pm
You are experiencing the results of 'implicit' conversion. See BOL under "implicit data type conversions".
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply