SQL Server 2008 and Crystal Reports XI
I'm not exactly sure why I can't convert the values. I have tried cast and convert which both result in the same error. This column did initially contain alphanumeric values but after my primary query inserts values into a temp table there are only numbers in there.
The conversion
CASE WHEN ISNUMERIC(myfield) = 1 THEN CAST(myfield AS INT) ELSE 0 END
returns zeros. An example of the data in the column is:
65
89
151
175
210
I'm sure I'm missing something obvious but I can't figure it out. In crystal reports I was able to successfully use:
IF isnumeric({myfield}) then tonumber({myfield}) else 999
That converts successfully without any issues. Why would it work in crystal and not in SQL?
The answer to this was there was a hidden carriage return in the varchar column so I used:
REPLACE(myfield, CHAR(13), ''),
and was able to convert it without any trouble.