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 > 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?