CODEWITHSUNDEEP
sqlsql-server
Alex Gordon
Alex Gordon

Reputation: 60811

SQL Server: finding the bad data

How do I select all rows except for ones that where I get an error calling CONVERT on one of the columns?

For example, I am doing this:

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

And I am getting errors for some of the values. I have two questions:

  1. How can I skip the rows that have errors?
  2. How can I get only the rows that have errors?

Upvotes: 5

Views: 4763

Answers (2)

bobs
bobs

Reputation: 22204

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

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

Upvotes: 10

cbattlegear
cbattlegear

Reputation: 854

I believe you can utilize the IGNORE statement for this, I may be wrong though.

Upvotes: -1

Related Questions