Conversion failed

Transact-SQL
1

Dear Team,

We create a table (EMP_NKSINGH) in SQl Server 2008. Table Structure as follows :slight_smile:

EMP_ID Varchar 50
EMP_NAME Varchar 50
EMP_DEPT Varchar 50
EMP_BASIC Varchar 50

We add a Column EMP_EMP_BASIC DECIMAL (15,2) in same table.
Now I want to update EMP_EMP_BASIC with all records of EMP_BASIC with below query

UPDATE EMP_NKSINGH
SET EMP_EMP_BASIC = (SELECT EMP_BASIC FROM EMP_NKSINGH R WHERE EMP_NKSINGH.EMP_ID = R.EMP_ID)

then we get an error :slight_smile:

Msg 8115, Level 16, State 8, Line 1
Arithmetic overflow error converting varchar to data type numeric.
The statement has been terminated.

Please help!!!

2

First, something simple way:

UPDATE 
    EMP_NKSINGH
SET 
   EMP_EMP_BASIC =  EMP_BASIC

Probably, there is some value in EMP_BASIC that can not be converted to decimal.
Use TRY_CONVERT to cast to DECIMAL(15,2) and find the records that are fit in it

EMP_EMP_BASIC = TRY_CONVER(DECIMAL(15,2),EMP_BASIC)

You can find it with this:

SELECT
    EMP_ID,EMP_BASIC
FROM
    (SELECT 
         EMP_ID,
         EMP_NAME,
         EMP_DEPT,
         EMP_BASIC,
         TRY_CONVER(DECIMAL(15,2),EMP_BASIC) AS  EMP_EMP_BASIC
     FROM
         EMP_NKSINGH AS E
     )E
WHERE
    E.EMP_EMP_BASIC IS NULL
3

Thanks to your quick reply,

I would like to inform you that i'm Using SQL Server 2008 thus we get an error TRY_CONVERT is not a recognized built-in function name.

and when we are using CONVERT function instead of TRY_CONVERT then we get an error :slight_smile:

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

Records as below:

EMP_ID EMP_NAME EMP_DEPT EMP_BASIC
EMP_HR_1007 SAVITRI SINGH HUMAN RESOURCE 57500.00
EMP_HR_1009 SUMITRA SINGH HUMAN RESOURCE 78,020.00
EMP_HR_1013 SABNAM SINGH HUMAN RESOURCE 36500
EMP_HR_1018 NEERU SINGH HUMAN RESOURCE 42000
EMP_IT_10015 ANITA SINGH INFORMATION TECH 25500.50
EMP_IT_10025 SINDHU SINGH INFORMATION TECH 30500.40

4

My bad with try_convert (new in 2012).

78,020.00 it is a comma there