Conversion failed when converting the varchar value to data type int.

Question

Post reply

Conversion failed when converting the varchar value to data type int.

diglife

Valued Member

Points: 63
More actions
October 21, 2021 at 1:52 pm

#3942833

Hi,
I've tried using cast but can't seem to get the syntax right. Here is the query below:
SELECT PurchaseOrder AS Customer, [JobNumber] AS ROM#, Counter_Type as Countertop, ((Length/12)*(16)) as Price, LnFt AS Reason,
CASE WHEN LnFt = '1' THEN ' SHIPPING DAMAGE' WHEN LnFt = '2' THEN 'SHIPPING SHORT ON DELIVERY' WHEN LnFt = '3' THEN 'INSTALLATION ERROR' WHEN LnFt LIKE '%4%' THEN 'MANUFACTURING'
WHEN LnFt = '5' THEN 'SALES ERROR' WHEN LnFt = '6' THEN 'FS ERROR' WHEN LnFt = '7' THEN 'WARRANTY-PUNCH' WHEN LnFt = '8' THEN 'ORDER ENTRY' WHEN LnFt = '9' THEN
'CUSTOMER CONCESSION' ELSE 'MISSING REASON CODE' END AS ReasonDescription
FROM dbo.Countertops
WHERE ([JobNumber] LIKE '%R%') AND (produced >= '2021-09-04 16:24:20.700')
GROUP BY PurchaseOrder, [JobNumber], Counter_Type, length, LnFt
ORDER BY Customer
I know it's : ((Length/12)*(16)) as Price causing the issue but can't seem to get the syntax with CONVERT OR CAST right. Can anyone help?
Thanks!

Viewing 6 posts - 1 through 5 (of 5 total)

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

What is the datatype of Length?

What datatype would you like the calculation to return?

Your problem is likely to be integer mathematics. Why not just use

Length * 0.75

in any case? This will force the result to be a decimal.

This reply was modified 3 years, 1 month ago by Phil Parkin.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 2

Could you post the DDL for the tables? Mostly, I am wondering what the datatype on "Length" is. If it is not a numeric data type (numeric, decimal, float, int, etc), then you would need to filter out any cases where Length is non-numeric (such as 'NA').

The error is pretty clear - you have some value that cannot be cast from VARCHAR to INT. There is literally no way to convert "hello world" to an INT (for example) and that is what the error is telling you.

EDIT - thought I should clarify how I would fix it. I'd add to your WHERE clause something like:

AND ISNUMERIC(Length) = 1

This reply was modified 3 years, 1 month ago by Mr. Brian Gale. Reason: adding potential fix

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

diglife Valued Member Points: 63 More actions · Answer 3

Thanks for the response, i was able to get the solution:

((CAST(Length AS Float)/12)*(16.33)) as Price

Thank you for the help.

Jeff Moden SSC Guru Points: 1004355 More actions · Answer 4

diglife wrote:

Thanks for the response, i was able to get the solution:
((CAST(Length AS Float)/12)*(16.33)) as Price
Thank you for the help.

You title said you were trying to cast to an INT. Price is an INT???

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 5

Jeff - My GUESS is that it was an implicit conversion due to the division by int. I expect that Length is a CHAR or VARCHAR datatype, but holds FLOAT data.

I just did a test of this and that is what it looks like to me:

DECLARE @test VARCHAR(4) = '10.1'
SELECT (@test/10)*10.1

The above will throw an error about converting VARCHAR to INT in SQL 2016 (and likely other versions). BUT if you cast @test-2 as a FLOAT (or numeric or decimal or ...), then it works fine.

My guess is that it is how SQL is parsing it. VARCHAR/INT will try to convert the VARCHAR to an INT and 10.1 cannot be converted to INT.

An alternate approach to CASTing would be to change the INT (12) to a DECIMAL (12.0 for example). Then the VARCHAR will be implicitly CAST to the same datatype. May give you too many decimal places though...

On a different note, I am not a big fan of "magic numbers". I would much rather store 12 and 16.33 in a variable so it is easy to tell what they represent UNLESS there is some comment above them explaining what they are. But something like @exchangeRate and @quantity (I'm just guessing that is what 16.33 and 12 represent) is MUCH easier for a developer to look at the code and know what it is doing than having numbers without representation in a query.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.