SQL Server: Finding Bad Data

Question

How to select all rows except those where I get an error message that causes CONVERTin one of the columns?

For example, I do this:

SELECT rowid 
FROM batchinfo 
WHERE CONVERT(DATE, reporttime, 101) between '2010-07-01' and '2010-07-31';

And I get errors for some values. I have two questions:

• How to skip error lines?
• How to get only error strings?

Answer 1

You can use the ISDATE () function to check the values.

SELECT *
FROM MyTable
WHERE ISDATE(MyColumn) != 1

Answer 2

I believe you can use the IGNORE statement for this, but I could be wrong.