You don't happen to be able to use SQL 2012 do you? If so there are some new parse functions that you could use to make life simpler. If not then you could use the LEN function in combination with a LIKE clause to get the values you want. You might also be able to use the ISDATE function. But, the ISNUMERIC function probably will not work. Here is an example of the differences between ISNUMERIC and a numeric LIKE predicate:DECLARE @Foo TABLE (Val VARCHAR(50))
INSERT @Foo (Val)
VALUES
('1')
,('1,000')
,('5e3')
,('100')
,('100.00')
,('9781297')
,('9781e297')
,('978w1297')
,('asdfg')
,('.9781297')
,('12d3')
,('$123,456.00')
,(' 12 ')
,(char(10))
,('$')
,(NULL)
SELECT
Val
,ISNUMERIC(Val) AS IsValNumeric
,CASE WHEN Val NOT LIKE '%[^0-9]%' THEN 1 ELSE 0 END AS IsInt
FROM @Foo
FOr your specific code, here is something that might work. However, you may still run into issues with when SQL applies teh conversion versus when it applies teh predicate:DECLARE @testdata TABLE
(
data nvarchar(20) NULL
)
Insert into @testdata
select '20121017'UNION all
select '20121015'UNION all
select '20121016'UNION all
select 'zxdf'UNION all
select 'a'
SELECT data
FROM @testdata
WHERE LEN(data) <> 8
OR data LIKE '%[^0-9]%'
-- OR
SELECT data
FROM @testdata
WHERE ISDATE(data) = 0
SELECT CAST(data AS DATETIME)
FROM @testdata
WHERE LEN(data) = 8
AND data NOT LIKE '%[^0-9]%'
-- Or
SELECT CAST(data AS DATETIME)
FROM @testdata
WHERE ISDATE(data) = 1
EDIT: I was showing how to get only the dates, changed to show invalid date values