Conversion failed when converting the nvarchar value to data type int

Question

Conversion failed when converting the nvarchar value to data type int

wooricom

SSC Enthusiast

Points: 194
More actions
August 15, 2012 at 1:36 pm

#262889

I got "Conversion failed when converting the nvarchar value to data type int" error when I execute below SQL.
I guess nvarchar value maybe warranty field and try to write to data type int.
please tell me which part of sql was wrong.
SELECT DISTINCT TOP (100) PERCENT PONumber, Warranty, ISNULL(Warranty,'0') - DATEDIFF(day, Received, GETDATE()) AS WarrantyLeft FROM dbo.partlist WHERE (Warranty IS NOT NULL) AND (isnumeric(Warranty) ='1') AND (Received IS NOT NULL) GROUP BY PONumber, Warranty, Received ORDER BY PONumber

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

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

wooricom (8/15/2012)
I got "Conversion failed when converting the nvarchar value to data type int" error when I execute below SQL.
I guess nvarchar value maybe warranty field and try to write to data type int.
please tell me which part of sql was wrong.
SELECT DISTINCT TOP (100) PERCENT PONumber, Warranty, ISNULL(Warranty,'0') - DATEDIFF(day, Received, GETDATE()) AS WarrantyLeft FROM dbo.partlist WHERE (Warranty IS NOT NULL) AND (isnumeric(Warranty) ='1') AND (Received IS NOT NULL) GROUP BY PONumber, Warranty, Received ORDER BY PONumber

You may want to read this article: http://www.sqlservercentral.com/articles/IsNumeric/71512/

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

Apart from Lynn recommendation, you don't need to add "ISNULL(Warranty,'0')" if you have "WHERE (Warranty IS NOT NULL)".

This won't solve your problem (in the article they show your possible solution) but it's just a simple observation.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

wooricom SSC Enthusiast Points: 194 More actions · Answer 3

:-):-)

Thank Lynn Pettis and Luis Cazares,

I fixed this problem, I use IsAllDigites function instead of using isnumeric function.