Casting fails on subquery result I'm in a bit of a pickle, I have to perform a query on a database with rather poor data quality. I am trying to evaluate a field which contains a date, but which was defined as a nvarchar. I thought I would be able to cast the value to an integer but this fails as there also non-date values in the date field (random letters and signs,etc...). 

What I decided to do is as follows:

SELECT q.foo, cast( q.bar as int) 
FROM ( SELECT foo,bar FROM table where ISNUMERIC(bar) = 1) as q 
WHERE q.bar &gt; 20140401


But this still crashes stating conversion failed on the nvarchar value '.       '. I expected that all values would  be castable to int as they were numeric?
You can wrap a CASE inside the CAST(), so the CAST() is only performed on valid numeric values - the other ones will result in NULL unless you put an ELSE in there somewhere.

Also, your conversion error probably stems from the WHERE clause, where q.bar is implicitly converted to int in order to compare it to 20140401 (which is an int).

This will probably solve your problem:

SELECT q.foo, CAST((CASE WHEN q.bar NOT LIKE '%[^0-9]%' THEN q.bar END) AS int) 
FROM ( SELECT foo,bar FROM table) as q 
WHERE q.bar &gt; '20140401';