Link to home
Start Free TrialLog in
Avatar of Software Engineer
Software Engineer

asked on

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

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

ASKER CERTIFIED SOLUTION
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
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?
Avatar of Software Engineer
Software Engineer

ASKER

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

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) 

> "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. 


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
Thanks, Jim!