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?

  • BOL states ISNUMERIC returns 1 for some characters that are not numbers, such as plus (+), minus (-) so I would imagine this is possibly the issue you are seeing Apr 16 '14 at 9:34
  • 1
    And there's a good answer to this on StackOverflow. If you're on 2012, use TRYPARSE or TRYCONVERT Apr 16 '14 at 9:36
  • Unfortunately I'm on 2008 :( Apr 16 '14 at 9:41
  • Ok then, create your own helper function? Apr 16 '14 at 9:51

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';
  • Great to see the correct all-digits test used in place of the inadequate ISNUMERIC() function. Aug 1 '14 at 21:17

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'

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

  • My understanding is that a subquery is evaluated before the SELECT clause. I'll find a work around. Aug 1 '14 at 22:20
  • @PaulWhite Added a CASE to the SELECT clause and removed the subquery since it's unnecessary and just adds complication to query. I did keep the ISDATE in the WHERE clause so only valid results will be returned. Aug 2 '14 at 0:38

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]'
  • 4
    This assumes the WHERE clause is applied before the expression in the SELECT clause is evaluated, which is not guaranteed.
    – Paul White
    Jul 20 '14 at 6:31
  • 1
    Also this allows "dates" like '19/39/9765'. Aug 3 '14 at 18:36
  • @PaulWhite For my own curiosity: Is it guaranteed if the WHERE is moved to a sub-query/CTE?
    – ckerth
    Sep 5 '14 at 13:50
  • 1
    @ckerth No it is not.
    – Paul White
    Sep 6 '14 at 1:05

Not the answer you're looking for? Browse other questions tagged or ask your own question.