Conversion Failed When Converting The Varchar Value 'Id' 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 'Id' to data type int

In your Join condition am sure one column is of Integer type and other one is Varchar type.

ON History.Id = Header.docId

since Int has higher precedence than varchar, Varchar column will be implicitly converted to Int

So explicitly convert the Int column to varchar.

ON History.Id = cast(Header.docId as varchar(50))

I have considered Header.docId as Int type if no, then convert History.Id to varchar

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

you need to use DynamicSQL:

declare @AllIDs nvarchar(max)
set @AllIDs = '('

select 
    @AllIDs = (@AllIDs + cast(ID as nvarchar(10)) + ',') 
from EmployeeDetail WHERE Code like 'ABC%'

set @AllIDs = @AllIDs + '-555)'

declare @sql nvarchar(max) = ' select * from Tbl_OT where EmployeeId In ' + @ALLIDs + ' AND OTDate = ''2016-11-06'''

print @sql
exec sp_executesql @sql,N''

The -555 as the last ID in the list is there to add to the last comma which otherwise will break syntax. Use some non-existing ID there.

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 'something' to data type int

An expression is an expression is an expression. And -- like the colors of many roses -- has only one type. It cannot be both a string and a date.

By the type precedence rules of SQL, when a string and a number are the choices, the number is the dominant type; so the string is converted to a number. And you get a conversion error.

So, use CONVERT(), CAST() or FORMAT():

(CASE WHEN CreatedDateTime IS NOT NULL AND migrationdatetime IS NOT NULL
      THEN CONVERT(VARCHAR(255), DATEDIFF(DAY, CreatedDateTime, MigrationDateTime))
      ELSE 'not moved to prd'
 END)

Of course, given the rules of NULLs in SQL, this is more simply written as:

      COALESCE(CONVERT(VARCHAR(255), DATEDIFF(DAY, CreatedDateTime, MigrationDateTime)),
               'not moved to prd'
              )

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 '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

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))

---

Related Topics