CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Sixthsense
Sixthsense

Reputation: 1975

Conversion failed when converting the nvarchar value to data type int - Error Message

My Query

Select * from MyTable

The table consists 300k rows. It runs for 200k+ rows and this error pops up.

enter image description here

How to handle this to get the full data?

Does MyTable have any computed columns?

Table consists of a computed column with the name IsExceeds which is given below for your reference.

enter image description here

This is the computed column formula:

(CONVERT([int],[Pro_PCT])-CONVERT([int],replace([Max_Off],'%','')))

Field Definitions:

[Pro_PCT] [nvarchar](50) NULL,
[Max_Off] [nvarchar](50) NULL,
[IsExceeds]  AS (CONVERT([int],[Pro_PCT])-CONVERT([int],replace([Max_Off],'%','')))

Upvotes: 1

Views: 2705

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

Why are you storing amounts as strings? That is the fundamental problem.

So, I would suggest fixing your data. Something like this:

update mytable
    set max_off = replace(max_off, '%');

alter table mytable alter pro_pct numeric(10, 4);
alter table mytable alter max_off numeric(10, 4);

(Without sample data or an example of the data I am just guessing on a reasonable type.)

Then, you can define IsExceeds as:

(Pro_PCT - Max_Off)

Voila! No problems.

Upvotes: 3

Saket Yadav
Saket Yadav

Reputation: 1017

Kindly convert in float then convert into int.

declare @n nvarchar(20)
set @n='11.11'

if (isnumeric(@n)=0)
 SELECT 0
   else 
      SELECT CAST(CONVERT(float, @n) as int) AS n

Upvotes: 3

Mathias F
Mathias F

Reputation: 15891

Check the bad data with 

select  * from MyTable where isnumeric(  Max_Off ) = 0

Upvotes: 2

Zohar Peled
Zohar Peled

Reputation: 82474

Based on the formula - either Pro_PCT or Max_Off contains the value 11.11 (well, with an extra % for Max_Off. Perhaps they also contain other values that can't be converted to int.

Here's what you can do to find all the rows that will cause this problem:

Select * 
from MyTable
where try_cast(Pro_PCT as int) is null
or try_cast(replace([Max_Off],'%','') as int) is null

After you've found them, you can either fix the values or change the calculation of the computed column to use try_cast or try_convert instead of convert.

Upvotes: 2

Related Questions