Conversion failed when converting the varchar value to data type int ошибка - Ремонт и установка крупной бытовой техники

I am struggling for a few days with this issue and I can’t figure out how can I fix it.

I would like to group by my table on values 1,2,3,4,5 so I have created a temporary table with this values.

Now I have to INNER JOIN this table with other tables on a.value = #myTempTable.num.

BUT a.value is ntext so I need to CONVERT it what I actually did, but I am getting an error:

Conversion failed when converting the varchar value ‘simple, ‘ to data
type int. (on line 7)

Create table #myTempTable
(
num int
)
insert into #myTempTable (num) values (1),(2),(3),(4),(5)

 SELECT a.name, CONVERT(INT, CONVERT(VARCHAR(12), a.value)) AS value, COUNT(*) AS pocet   
 FROM 
 (SELECT item.name, value.value 
  FROM mdl_feedback AS feedback 
  INNER JOIN mdl_feedback_item AS item 
       ON feedback.id = item.feedback
  INNER JOIN mdl_feedback_value AS value 
       ON item.id = value.item 
   WHERE item.typ = 'multichoicerated' AND item.feedback IN (43)
 ) AS a 
 INNER JOIN #myTempTable 
     on CONVERT(INT, CONVERT(VARCHAR(12), a.value)) = #myTempTable.num
 GROUP BY a.name, CONVERT(INT, CONVERT(VARCHAR(12), a.value)) ORDER BY a.name

 drop table #myTempTable

I am not getting this error without the last INNER JOIN

INNER JOIN #myTempTable on CONVERT(INT, CONVERT(VARCHAR(12), a.value))
= #myTempTable.num

Could someone help me please?

Thanks.

Источник

Remove From My Forums

Question
Dear All,

When I am firing a below query I am getting the error

SELECT
Empcode,
Date_of_Joining,
LastWorkingDate,
Tenure,
CASE WHEN CAST( Tenure AS INTEGER) =1 THEN ‘THREE’ ELSE Tenure END TEN

FROM #TEMP

tenure I AM getting bu substracting lastworking date to date of joining

Error:-

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value ‘THREE’ to data type int.

Regards,

Vipin jha

Thankx & regards, Vipin jha MCP
- Changed type
  
  Monday, January 7, 2013 4:41 PM
  Question rather than discussion

Answers

Try this,

The problem is you are trying to mix int and varchar datatypes together.
```
SELECT 
Empcode,
Date_of_Joining,
LastWorkingDate,
Tenure,
CASE WHEN CAST( Tenure AS INTEGER) =1 THEN 'THREE' ELSE Convert(Varchar(100),Tenure) END TEN

FROM #TEMP
```
Regards
satheesh
- Proposed as answer by
  Kalman Toth
  Monday, January 7, 2013 6:40 PM
- Marked as answer by
  Allen Li — MSFT
  Monday, January 14, 2013 5:59 AM
Try
```
SELECT 
Empcode,
Date_of_Joining,
LastWorkingDate,
Tenure,
CASE WHEN Tenure NOT LIKE '%[^0-9]%' then when CAST( Tenure AS INTEGER) =1 THEN 'THREE' 
else cast(Tenure as varchar(20)) end
ELSE cast(Tenure as varchar(20)) END As TEN
```
For every expert, there is an equal and opposite expert. — Becker’s Law

My blog
- Proposed as answer by
  Kalman Toth
  Monday, January 7, 2013 6:40 PM
- Marked as answer by
  Allen Li — MSFT
  Monday, January 14, 2013 5:59 AM

Источник

Remove From My Forums

Question
Hi, I am working on a report which is off of survey information and I am using dynamic pivot on multiple columns.

I have questions like Did you use this parking tag for more than 250 hours? If yes specify number of hours.
and the answers could be No, 302, 279, No and so on….

All these answers are of varchar datatype and all this data comes from a partner application where we consume this data for internal reporting.

When I am doing dynamic pivot I get the below error.

Error: Conversion failed when converting the varchar value ‘No’ to data type int.

Query
```
DECLARE @Cols1 VARCHAR(MAX), @Cols0 VARCHAR(MAX), @Total VARCHAR(MAX), @SQL VARCHAR(MAX)

SELECT @Cols1 = STUFF((SELECT ', ' + QUOTENAME(Question) FROM Question GROUP BY Question FOR XML PATH('')),1,2,'')  

SELECT @Cols0 = (SELECT ', COALESCE(' + QUOTENAME(Question) + ',0) as ' + QUOTENAME(Question) FROM Question GROUP BY Question FOR XML PATH(''))

SET @SQL = 'SELECT QID, QNAME' + @Cols0 + '
FROM (SELECT QID, QNAME, ANSWERS, Question
FROM Question) T
PIVOT (MAX(ANSWERS) FOR Question IN ('+@Cols1+')) AS P'
 
EXECUTE (@SQL)
```
I am using SQL Server 2008 R2.

Please guide me to resolve this.

Thanks in advance……….

Ione

Answers

create table questions (QID int, QNAME varchar(50), ANSWERS varchar(500),  Question varchar(50))

Insert into questions values(1,'a','b','c'), (2,'a2','b2','c2') 

DECLARE @Cols1 VARCHAR(MAX), @Cols0 VARCHAR(MAX), @Total VARCHAR(MAX), @SQL VARCHAR(MAX)

SELECT @Cols1 = STUFF((SELECT ', ' + QUOTENAME(Question) FROM Questions GROUP BY Question FOR XML PATH('')),1,2,'')  

SELECT @Cols0 = (SELECT ', COALESCE(' + QUOTENAME(Question) + ',''0'') as ' + QUOTENAME(Question) FROM Questions GROUP BY Question FOR XML PATH(''))

SET @SQL = 'SELECT QID, QNAME' + @Cols0 + '
FROM (SELECT QID, QNAME, ANSWERS, Question
FROM Questions) T
PIVOT (MAX(ANSWERS) FOR Question IN ('+@Cols1+')) AS P'
 
EXECUTE (@SQL)


drop table questions

Marked as answer by

Monday, February 9, 2015 4:59 PM

Источник

Try converting the @recid (has data type int) to varchar data type

set @TargetNumber = 'SELECT ' +  @NumberField + 
     ' FROM ' + @sourcetable +
     ' WHERE ' + @idfield + ' = ' + CAST(@recid AS varchar(20))

When you combine expressions with different data types, there are some rules for converting, from lower to higher precedence.

int = data type is at position 16
varchar data type is at position 27

See Data type precedence (Transact-SQL) in the product documentation.

Also, be aware of SQL Injection

Later edit:

as suggested by @sp_BlitzErik, the next problem will be

set @TargetNumber =

because @TargetNumber it is int data type, and you will have the same problem/error , different message : Conversion failed when converting the varchar value 'SELECT stepnum FROM tblsteps WHERE stepid = 3' to data type int.

This time, you need to change in declaretion section to :

declare @targetnumber varchar(500)

dbfiddle here

Источник

SQL Server error Msg 245, Level 16 tells us that there was a problem when trying to convert a value to a specific data type.

You’ll get this error if you try to insert the wrong data type into a column.

To fix this issue, make sure the data type of the value you’re trying to insert, matches the column’s type.

Example of Problem Code

Here’s an example of code that results in this error.

INSERT INTO Orders(OrderId, OrderDate, OrderDesc) 
VALUES ('2020-04-02', '2020-04-02', 'Dog food');

Result:

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '2020-04-02' to data type int.

In this case I tried to insert a date into the OrdersId column. We can assume by the error message that this column is an int column, but we should verify that.

If we look at the table definition, we can see the OrderId column’s type:

CREATE TABLE Orders (
    OrderId int NOT NULL,
    OrderDate date NOT NULL,
    OrderDesc varchar(255) NOT NULL,
    CONSTRAINT PKOrders PRIMARY KEY CLUSTERED(OrderId, OrderDate)
    );

As expected, the OrderId column is an int column.

Solution

To resolve this issue, we need to make sure that we’re inserting the correct value. We also need to ensure that the table definition is appropriate for the data that it needs to store. This will help enforce the data integrity of our database.

In our case, the column’s data type is correct. The problem was caused by accidentally trying to insert the wrong data.

Therefore, to fix the issue, we can change our INSERT statement to insert the correct data.

INSERT INTO Orders(OrderId, OrderDate, OrderDesc) 
VALUES (1, '2020-04-02', 'Dog food');

Result:

(1 row affected)

Success!

Источник