-1

I am trying the below SQL Query in a View

select 
    ShiftDate, empid, firstin, lastout, totalhrsfilo 
from 
    [View_Name] 
where 
    [ShiftDate] between '2016-06-01' and '2016-06-30' 
    and empid in (1, 2, 3, 4, 5, 6, 7, 8) 
    and remarks != 'Weekly Off 1' 
    and remarks != 'Weekly Off 2'

I get this error:

Conversion failed when converting the nvarchar value 'External' to data type int.

From this error I am unable to determine which column the conversion error is occurring in.

View structure:

enter image description here

How to find which column creates error and how to fix it?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
ManiMuthuPandi
  • 1,594
  • 2
  • 26
  • 46

2 Answers2

0

I think your problem is similar to this one : Conversion failed when converting the nvarchar value ... to data type int

select ShiftDate,empid,firstin,lastout,totalhrsfilo from [View_Name] where [ShiftDate] between '2016-06-01' and '2016-06-30' and Convert(int,empid) in (1,2,3,4,5,6,7,8) and remarks!='Weekly Off 1' and remarks!='Weekly Off 2'

If this doesn't work, please post the structure of your view so we can dig deeper.

Community
  • 1
  • 1
Robert Columbia
  • 6,313
  • 15
  • 32
  • 40
  • But your suggestion is correct. When I remove the EID validation from where clause it works well. Is there any other way to correct this – ManiMuthuPandi Jul 29 '16 at 04:22
0

I got answer for my question.

select ShiftDate,empid,firstin,lastout,totalhrsfilo from [View_Name] where [ShiftDate] between '2016-06-01' and '2016-06-30' and empid in ('1','2','3','4','5','6','7','8') and remarks!='Weekly Off 1' and remarks!='Weekly Off 2'

Empid is varchar field; After changing below condition its work

empid in ('1','2','3','4','5','6','7','8')
ManiMuthuPandi
  • 1,594
  • 2
  • 26
  • 46
  • You can additionally check for all rows where the empid column might be containing a non-numeric character. – The Shooter Jul 29 '16 at 04:31
  • Thank you; Do you mean like this 'empid > 0' – ManiMuthuPandi Jul 29 '16 at 04:39
  • Yes if any of your row for empid column contains string like 'empid > 0' you would get the exception. Why? The original query you had posted had numeric values in the condition. SQL Server would automatically try to convert the string data (empid column is string/nvarchar column) to numbers to do the comparison. Problem is it would convert all rows to numbers before it can do the comparison and if any of the row has such data the conversion would fail and you would get the error. – The Shooter Jul 29 '16 at 04:53
  • I have generated some data using that original query. Only for June month data I am getting error. I will change it in my app.Thanks for your information – ManiMuthuPandi Jul 29 '16 at 04:59