IsDate Function in SQL evaluates invalid dates as valid

Viewed 37595

I am running a SQL Statement against imported data from Excel Files. In this SQL I am checking if the users have entered dates properly by using IsDate function. Since this is a raw data that hasn't been converted yet, all dates are stored in a varchar data type field.

In some circumstances IsDate returns 1 (valid date) when there is clearly an incorrect date format entered by the user.

For Example:

07/001/2012

2012-07-002

007/002/2012

Any Suggestions on how to handle this problem?

SELECT *
  FROM tblImport
 WHERE (ISDATE(dt) = 0 
        AND (dt is not null AND dt <> ''))

Thanks!

p.s. Smacking users' did not help.

3 Answers
Related