T-SQL: Conversion failed when converting the varchar value '12646–112521' to data type int.
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,
What is the end result that you are looking to have when successful?
"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.
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
In such case the conversion must fail.