Post reply

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

  • 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!

     

  • October 21, 2021 at 1:57 pm

    #3942834

    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 2 years, 6 months ago by  Phil Parkin.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • October 21, 2021 at 2:01 pm

    #3942839

    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 2 years, 6 months 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.

  • October 21, 2021 at 2:30 pm

    #3942870

    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.

  • October 21, 2021 at 9:41 pm

    #3943027

    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)

  • October 21, 2021 at 10:05 pm

    #3943039

    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.

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

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