CODEWITHSUNDEEP
sqlsql-servert-sql
Sunlight
Sunlight

Reputation: 57

"Error converting to int" when casting to decimal

Why would I be getting an error message involving the data type INT when casting to decimal?

SELECT
    a.number, --nvarchar
    b.number  --nvarchar
FROM
    TableA AS a
    TableB AS b
WHERE
    CAST(COALESCE(a.number, 0), AS DECIMAL(10,2)) <> CAST(COALESCE(b.number, 0), AS DECIMAL(10,2))

The error:

Msg 245, Level 16, State 1, Line 156 Conversion failed when converting the nvarchar value '78000.0' to data type int.

Upvotes: 2

Views: 1615

Answers (2)

Brad
Brad

Reputation: 3591

Looks like because you are doing a Coalese on the number fields (which are nvarchar) and a number, your 0. Move your cast to inside the Coalese (or cast the column that is nvarchar as well.

Doing this, gives me a conversion error because you can not coalese a string and a number

SELECT COALESCE('A', 0) -- errors out, and this is basically what your doing

SELECT
    a.number, --nvarchar
    b.number  --nvarchar
FROM
    TableA AS a
    TableB AS b
WHERE
COALESCE(CAST(a.number AS DECIMAL(10,2)), 0) <> COALESCE(CAST(b.number AS DECIMAL(10,2)), 0)

Upvotes: 1

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726499

This happens because coalesce expects a.number, an nvarchar, to be compatible with 0, an int. That is where the conversion fails, because nvarchar and int are incompatible.

Replacing 0 with '0' should fix the problem:

CAST(COALESCE(a.number, `0`), AS DECIMAL(10,2))

Note: Unless this is a legacy database of which you have no control, you should consider changing the type of the number field to be, well, a number - for example, a decimal(10,2).

Upvotes: 1

Related Questions