Error converting data type varchar to numeric issue

Question

Error converting data type varchar to numeric issue

JeremyU

SSChasing Mays

Points: 640
More actions
August 3, 2022 at 11:46 pm

#4072214
I tried a lot of things to fix it, and no success.
I have a field called MASTER_BOL_NUMBER . According to documentation it is CHAR.

I see that inside it has only blanks and numbers

When I try to CAST( MASTER_BOL_NUMBER as numeric)
I am getting an error "Error converting data type varchar to numeric."
I tried also smth like that
```
CAST( IIF(MASTER_BOL_NUMBER='',0,MASTER_BOL_NUMBER) as numeric)
```
and this
```
CAST( IIF(MASTER_BOL_NUMBER IS NULL,0,MASTER_BOL_NUMBER) as numeric)
```
Also no success, I really don't understand why I am getting this error because usually CAST or CONVERT fix this data type issues.
Does someone else know what may help but for those functions?

Viewing 3 posts - 1 through 2 (of 2 total)

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

Ed B Hall of Fame Points: 3666 More actions · Answer 1

try this. or NUMERIC

TRY_CAST(MASTER_BOL_NUMBER AS INT)

ISNULL(TRY_CAST(MASTER_BOL_NUMBER AS INT),0)

This reply was modified 2 years, 3 months ago by Ed B.

kaj SSCommitted Points: 1605 More actions · Answer 2

The default precision and scale of the numeric type is 18,0 and my guess is that one or more rows have a value that exceed the default precision of 18.

As Ed B wrote you can identify the rows that elicit the error message by using TRY_CAST and check for null, i.e.

SELECT *

FROM yourTable

WHERE TRY_CAST(MASTER_BOL_NUMBER AS NUMERIC) IS NULL

Once you have identified the rows in error, you can then take the appropiate actions. For example you could increase the precision and thereby allow bigger numbers.

decimal and numeric (Transact-SQL) - SQL Server | Microsoft Docs