Casting fails on subquery result [duplicate]

Question

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?

Answer 1

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 > '20140401';

Answer 2

If you have a date table the following could work. If the date table does not already have a column for the date format stored in table.bar then create the column or cast.

select t.foo, t.bar
from table t
join date_table dt on t.bar like '%' + dt.DateID +'%'
where dt.DateID > 20140401

Since you would be using a date table it would probably be better to write the query like this.

select t.foo, dt.date_column as [bar]
from table t
join date_table dt on t.bar like '%' + dt.DateID +'%'
where dt.date_column > '20140401'

Answer 3

Give isdate a try. Here is the test I tried:

CREATE TABLE Foo (Id int not null identity(1,1), stringdate varchar(30))

INSERT INTO Foo VALUES ('1/1/2014'),('abc'),('01012014'),('1114'),('20140101')

SELECT stringdate, CASE WHEN ISDATE(stringdate) = 1 THEN cast(stringdate as datetime) END
FROM Foo 
WHERE ISDATE(stringdate) = 1 

This only returned entries for '1/1/2014' and '20140101' because they were the only ones I could validly convert to a date. Note: The behavior of isdate will depend on certain settings on your server. SET LANGUAGE for example but should handle the problems you are having.

Link to BOL for isdate: http://msdn.microsoft.com/en-us/library/ms187347(v=sql.100).aspx

Answer 4

In which format are the date values stored?

If you can determine the used format you can CONVERT with the specified format by resticting the rows using LEN and LIKE predicate.

Example: If you have the date values stored like '10/23/2016' you can use U.S. standard (101)

 SELECT [foo], CONVERT(DATETIME, [bar], 101)
 FROM [table]
 WHERE LEN([bar]) = 10 AND [bar] LIKE '[0-1][0-9]/[0-3][0-9]/[0-9][0-9][0-9][0-9]'