Conversion Failed When Converting the Varchar Value 'Simple, ' to Data Type Int

Conversion failed when converting the varchar value 'simple, ' to data type int

In order to avoid such error you could use CASE + ISNUMERIC to handle scenarios when you cannot convert to int.

Change

CONVERT(INT, CONVERT(VARCHAR(12), a.value))

To

CONVERT(INT,
        CASE
        WHEN IsNumeric(CONVERT(VARCHAR(12), a.value)) = 1 THEN CONVERT(VARCHAR(12),a.value)
        ELSE 0 END) 

Basically this is saying if you cannot convert me to int assign value of 0 (in my example)

Alternatively you can look at this article about creating a custom function that will check if a.value is number: http://www.tek-tips.com/faqs.cfm?fid=6423

Conversion failed when converting the varchar value of string to data type int

The second parameter in IIF is int and 'ONWARDS' is string. IIF expects both to be of same data type. Try converting the first parameter to string and it may work.

Something like this

SELECT description, moreThanDay, 
CONCAT(moreThanDay+1,' - ' , 
  IIF(LEAD(moreThanDay) OVER (ORDER BY moreThanDay) > 0, 
    STR(LEAD(moreThanDay) OVER (ORDER BY moreThanDay), 10),'ONWARDS')) AS rangeDay 
FROM EquipmentOwnerDetention WHERE equipmentOwnerCode=@equipmentOwnerCode 

Conversion failed when converting the varchar value 'U' to data type int

You can go about this in a couple of different ways:

Since the fields are in character format, replace 'u' with '0' and then convert it into int:

CAST(Replace(q1_c,'u','0') as int)

Or if you wanted to create a new calculated field you can directly create an integer field:

Case when q1_c='u' then 0 else CAST(q1_c as int) end as q1_c_calc

Hope this helps.

Conversion failed when converting the varchar value to data type int - but I'm not converting anything

Try:

SELECT @sql = 'SELECT * FROM ' + @ActualTableName + ' WHERE [FormID] = ' + CONVERT(NVARCHAR,@FormID)  + ';'

SQL is trying to convert all of the strings you're attempting to concatenate into INT values because @FormID is an INT and you're using the + operator - this is quite common when concatenating values which are a mixture of string and non-string types. You therefore need to explicitly tell SQL that the INT value should be treated as a string-type value for the purposes of the concatenation.

Conversion failed when converting the varchar value 'none' to data type int

If your tables actually consist of None and NA, then try this :

ALTER PROCEDURE spvec
AS
SELECT Row_number() OVER (ORDER BY (SELECT 1)) AS 'RNumber'
    ,vf.*
FROM LOGIN ld
    ,vehicle vf
WHERE (ld.fid = cast(vf.fid as varchar(max)))
    AND cast(ld.id as varchar(2)) <> 'NA'
    AND cast(vf.CustID as varchar(4)) <> 'None'
    AND cast(vf.Simo as varchar(4)) <> 'None'
ORDER BY Row_number() OVER (ORDER BY (SELECT 1))

Conversion failed when converting the varchar value 'C000000055' to data type int

Without single quotes the 3199 you're comparing it to is an INT. It's attempting to convert the value into an INT to do the comparison, and one of your values (C000000055) cannot be converted to an INT.

Add single quotes (') around 3199 to compare it as a VARCHAR:

Select  Cast(co_num As Varchar)
      , activity_date
      , due_date
From    coitem_log_mst
Where   Cast(co_num As Varchar) = '3199' 
        And co_line = 10
        And co_release = 0
Order By activity_date;

Conversion failed when converting the varchar value '0.87' to data type int

try this:

    SELECT  Admin1 AS Code, 
            COUNT(ID) AS Num_ID,
            SUM(Value) AS Value,
            SUM(PREMIUM) AS Premium,
            SUM(try_convert(float,USER1)) AS Income,
            COALESCE(SUM(PREMIUM)/(SUM(try_convert(float,ISNULL(USER1,0)))*0.715),0) AS 'Uplift',
            SUM(CAST(USER1 AS float))/SUM(PREMIUM) AS 'Ratio'    
    FROM #Customer_Values CV
    INNER JOIN CUSTOMER_07_2019.dbo.Customer_Details CD ON CV.ID = CD.LOCID
    INNER JOIN CUSTOMER_07_2019.dbo.Address AD ON CD.AddressID = AD.AddressID    
    GROUP BY Admin1

---

Related Topics