CODEWITHSUNDEEP
sqlsql-servert-sqltype-conversionimplicit-conversion
Tim Smith
Tim Smith

Reputation: 29

Why does implicit conversion from some numeric values work but not others?

This is something that has only just started happening today for me.

If I run this:

select '185.37' * 2.00 
select '0.16' * 2.00

The first query returns this error:

Arithmetic overflow error converting varchar to data type numeric.

Where as the second query returns 0.32 as expected.

Results

Messages

This is on SQL Server 15.0.2080.9.

I know I can fix the issue by casting to decimal or numeric, but why does the error only occur for the higher numbers? And why has this only started occurring today? This query has been used as part of an ETL that we've been using without changing anything for the past few years.

Upvotes: 0

Views: 1064

Answers (2)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239824

It tries to convert your string to a numeric(3,2) because that's the type on the right of the multiplication1. If you can force your value to be a larger numeric type:

select '185.37' * (102.00 - 100.00)

Then it works fine (produces 370.74) because it'll now attempt to convert it to a numeric(5,2).

Rather than doing it by a trick, however, I'd pick an appropriate numeric type for this and explicitly perform the required conversion, to better document what you want to occur:

select CONVERT(numeric(5,2),'185.37') * 2.00

1And it has a higher precedence.

EDIT (by Gordon Linoff):

SQL Server's use of type precedence for this purpose is explicitly stated in the documentation:

For comparison operators or other expressions, the resulting data type will depend on the rules of data type precedence.

There might be just a little confusion because the documentation is not clear that the scale and precision of numerics is explicitly part of the type (that is, what gets converted is numeric(3, 2) rather than numeric(?, ?) with appropriate scale and precision).

Upvotes: 2

Rohit Garg
Rohit Garg

Reputation: 51

For below mentioned SQL Left side is varchar datatype and right side is a numeric datatype numeric(3,2) .

select '185.37' * 2.00

After implicit conversion from varchar to numeric, result data type will be numeric(3,2) but actual result 370.74 need a datatype of numeric(5,2) so this expression fails with overflow error. you can see the datatype of these in below sample code.

select 2.00 as colDatatype,185.37 as colDatatype2, 370.74 as colDatatype3 into #tempdata

SELECT * FROM tempdb.INFORMATION_SCHEMA.columns WHERE table_name like'#tempdata%';

In such cases explicitly cast the expression to desired out datatype and not merely depend on implicit conversion of data types. You can refer the Datatype conversion chart on the website but here result data type after implicit conversion is not know.

Microsoft link

Upvotes: 0

Related Questions