0

Why is this simple code giving me an error:

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

select cast(value as INT)  
from CV3BasicObservation  (nolock)
where value >= 110
Brian Pressler
  • 6,653
  • 2
  • 19
  • 40
JGeorge
  • 17
  • 1

1 Answers1

5

SQL Server doesn't want to convert a number string that looks like a decimal to integer because you could lose precision. You could trick it with the round function:

select cast(round(value,0) as INT)  
from CV3BasicObservation  (nolock)
where cast(round(value,0) as INT) >= 110

NOTE: You have to do it to all instances of the field value where you are explicitly converting it to int or where it is implicitly converting it for comparison an int type value.

Brian Pressler
  • 6,653
  • 2
  • 19
  • 40
  • You have to do it to all instances that you used the field in your query. See edits to my answer. – Brian Pressler Dec 17 '15 at 21:57
  • I did add that, even then I am getting the same error - Error converting data type varchar to float. – JGeorge Dec 21 '15 at 17:01
  • The values where it is giving error is in 0.2 and <0.01 – JGeorge Dec 21 '15 at 17:36
  • You must have other non-numeric data in some of your fields. – Brian Pressler Dec 21 '15 at 18:12
  • These are the different types of values in this column 0.2 134 4.9 93 29 80 5.5 <0.01 1.3 <2 <70 06MAY08 06-11-05 ALX 1604 9.81 109.5 2130 EZF 6592 >220 2 0000 -68.7 – JGeorge Dec 21 '15 at 19:19
  • You have to have numeric values... you can't convert convert '<0.01', 'ALX', or '06MAY08' to an integer. – Brian Pressler Dec 21 '15 at 19:58
  • I do not want to convert them, I just need to filter the numeric values that are greater than or equal to 110 – JGeorge Dec 21 '15 at 20:41
  • I tried the following , But this is giving me an error 'An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown. select cast(Value as int) as value from CV3BasicObservation with (nolock) where 1 = case when value like '%[^0-9]%' then 0 when cast(value as int) > 110 then 1 else 0 end – JGeorge Dec 21 '15 at 20:44
  • Do you have SQL Server 2012? If so the TRY_PARSE function might be of help: https://msdn.microsoft.com/en-us/library/hh213126.aspx otherwise check out this post for filtering numeric data in strings http://stackoverflow.com/questions/312054/efficient-isnumeric-replacements-on-sql-server – Brian Pressler Dec 21 '15 at 21:41
  • I believe "CAST(ROUND(CAST('3.0' AS float), 0) AS int)" would be more effective. – Michael Erickson Mar 23 '16 at 18:28
  • They are identical in function. The string is implicitly converted to float and then rounded, but explicitly casting to float is more verbose so it's not a bad idea to make it clear what is going on. – Brian Pressler Mar 23 '16 at 20:13