We help IT Professionals succeed at work.

T-SQL: Conversion failed when converting the varchar value '12646–112521' to data type int.

Software Engineer
Software Engineer asked
on
Hi:

Below is a snippet from my T-SQL code.  It is giving me the following error:

Conversion failed when converting the varchar value '12646–112521' to data type int.

Please let me know how to add syntax to fix this.

Thanks!

Software Engineer

CASE WHEN ORDERLN.ITEMID IS NOT NULL THEN 
Ltrim(rtrim(ORDERLN.ITEMID)) + '–' + ltrim(rtrim(ORDERLN.LINEID))
ELSE '1' + '–' + ltrim(rtrim(ORDERLN.LINEID)) END as ITEMID,

Open in new window

Comment
Watch Question

View Solution Only

SQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015
Commented:
For starters..
  • Post the entire code that ran.
  • Run it, note the error message, double-click on the error message and watch the cursor jump to the line that threw the error.
  • Tell us the line that the cursor jumped to / threw the error.

Reason I ask this is because the code you posted all converts things to varchar's, which would not by itself cause a data type conversion error.

>Conversion failed when converting the varchar value '12646–112521' to data type int.
The dash character cannot be converted to an int, so wherever this is happening it needs to be identified and handled.   Perhaps the CASE block you posted is being INSERTed into an int column?
pcelba

Commented:
It seems you are comparing the value created in the above CASE statement which is a text string containing non-numeric character to integer value.

In such case the conversion must fail.
arnold
Distinguished Expert 2019

Commented:
Are you looking for the resulting computation of one number minus the other?

What is the end result that you are looking to have when successful?
Software Engineer

Author

Commented:
Hi Jim Horn:

"Perhaps the CASE block you posted is being INSERTed into an int column?"

Yes, you're correct.  So, what syntax do I need to add?

Software Engineer
David Johnson, CD
Distinguished Expert 2019

Commented:

do you want the value of the calculation of

12646–112521 = ‭-99,875‬?

or the string   12646–112521 converted into an int. (which will fail) 

Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:

> "Perhaps the CASE block you posted is being INSERTed into an int column?"
>  Yes, you're correct.  So, what syntax do I need to add?

Ok.  Then what do you want to insert,   12646, 112521, 12646112521, or ( {David's post above} 12646–112521 =) ‭-99875‬ ?


Numeric data types can only have 0-9, decimal, NULL, or dash if it's the first character for a negative number.   No dashes in the middle.  So you'll have to tell us. 


> Ltrim(rtrim(ORDERLN.ITEMID)) + '–' + ltrim(rtrim(ORDERLN.LINEID)) ELSE '1' + '–' + ltrim(rtrim(ORDERLN.LINEID)) END as ITEMID,


Also keep in mind that if you're doing numerical expressions such as subtraction then you'll have to lose the single quote marks ' converting these values to text, and CAST() the Ltrim(rtrim(ORDERLN.ITEMID))  values as a number, SQL Server will interpret that as string concatenation and not number math. 


Software Engineer

Author

Commented:
Hi Jim:

I deleted my table and recreated the ITEMID field as a varchar field, rather than an int field.  All is well, now.

Thank you!  Your guidance made me go down the successful path!

Software Engineer
Software Engineer

Author

Commented:
Thanks, Jim!