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

Question

I am getting this error when I run the case statement below. I think I have to convert at some point, but not sure where or how.

update [092018] 
set duration1 = duration 
where duration1 is null 
and (durationms > 100 and durationms < 1000)

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

Welcome to stackoverflow. Please take a minute to take the tour, especially How to How to Ask. Please note that sample data is best served as DDL + DML. Please edit your question to include it, your current attempt and your desired results. For more details, read this. — Zohar Peled
Could you paste the declaration of the table, please? I am pretty sure there is something wrong in the field datatype. — Carlos
COLUMN_NAME ORDINAL_POSITION durationms 11 DATA_TYPE varchar CHARACTER_MAXIMUM_LENGTH 50 CHARACTER_OCTET_LENGTH 50 — Phong Doan

Tim Biegeleisen Tim Biegeleisen · Accepted Answer · 2018-10-04T04:27:58

It sounds like your duarationms column is text of some sort. You may try casting it to integer:

UPDATE [092018]
SET duration1 = duration
WHERE duration1 IS NULL AND
    CAST(durationms AS int) > 100 AND CAST(durationms AS int) < 1000;

Note that for brevity you could have written the WHERE clause as:

WHERE duration1 IS NULL AND CAST(durationms AS int) BETWEEN 101 AND 999;

If you plan on doing arithmetic with the data contained in durationms, then consider making it a numeric/integer column.

Edit:

If the above query is still not working, then your data might be in even worse shape than we thought. There could be non numeric data in the durationms column. To flag out records which can't be cast to integer, use the following query:

SELECT *
FROM [092018]
WHERE TRY_CONVERT(int, durationms) IS NULL;

Any records returned should have durationms values which can't be coerced into integers.

If your version of SQL Server does not support TRY_CONVERT, then here is another option:

SELECT *
FROM [092018]
WHERE durationms LIKE '%[^0-9]%';

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

1 Answers