Arithmetic overflow error converting nvarchar to data type numeric when converting nvarchar to decimal

I have a column in sql which is sometimes nvarchar and number in some cases. The column has a datatype nvarchar . I am just interested in the places where its numeric where none numeric i dont need it. I use case like below

(SELECT case when isnumeric(dia) =1 then dia else '' end as days from dbo.Debtor ) 

the above query works fine and return the record where is numeric and empty where is null. However, i want to return my records as decimal or float. I because i want to be able to filter like

SELECT ..... WHERE EXISTS( (SELECT case when isnumeric(dia) =1 then dia else '' end as days from dbo.Debtor ) > 0 ) 

The above is just an example but didnt tested. Please my Problem now is when i cast my column dia to decimal i get the error "Arithmetic overflow error converting nvarchar to data type numeric".

Below is my query .

(SELECT case when isnumeric(dia) =1 then cast(dia as decimal(3,1)) else 0 end as de from dbo.Debtor ) 

Please any help would be appreciated. Thank you

4 Answers

In order to evaluate only numeric values you need to have isnumeric in where part. try something like

SELECT ISNULL((SELECT cast(dia as decimal(3,1)) from dbo.Debtor WHERE isnumeric(dia) =1 ),0) 

For anyone of you who is encountering this error, the solution is simple. If you can see in his example, he uses this convertion

cast(dia as decimal(3,1)) 

for example, he has a data which is 100.0007 his implicit conversion will result in the said error because he want three(3) numbers with one(1) decimal point only (3,1).

The solution to this is to actually checking the length of the data and determine, how many digits do you actually want.

In most cases this conversion is sufficient.

cast(dia as decimal(18,4)) 

But if your data has more numbers AFTER the decimal point, then change the conversion as needed.

cast(dia as decimal(18,5)) 

and so forth....

I assume your dbms is evaluating cast(dia as decimal(3,2)) in any case. To avoid that swap numeric check and cast:

SELECT cast(case when isnumeric(dia) = 1 then dia else null end as decimal(3,2)) from dbo.Debtor 

To understand this error, we need examples such as inserting into a table

Declare @@test table(test numeric(8,2)) 

But when you insert data with more than 8 characters Then the error message appears

insert into @@test values ('11190000.1212') 

Msg 8115, Level 16, State 8, Line 3 Arithmetic overflow error converting varchar to data type numeric. The statement has been terminated.