Monday, December 14, 2015

Conversion failed when converting the varchar value to data type int

I have a tableA
(ID int , Name Varchar(10), EmpId Varchar(10))


I store all numbers in Empid Column
Ex:
EmpID:
123
3445
4566
35476
etc..



There are few stored procedures which uses the same tableA,empid column data and do some conversions and suddenly the SP was failing 

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




somehow , some one entered data like below:
EmpID:
123
3445
4566
35476
34123.

YES, there was dot(period) added to the data.

so Resolution for issue was: 

First:Convert the Empid(Varchar) to Float and then to INT
cast(CAST(Empid AS FLOAT) as int

This fixed the issue perfectly instead of modifying the column Datatype, or updating data in table

No comments:

Post a Comment

https://blog.sqlauthority.com/2009/06/27/sql-server-fix-error-17892-logon-failed-for-login-due-to-trigger-execution-changed-database-context...