Query Studio - Could not convert date and/or time from string data type

Question

On one data extension, I have a DateIn field modeled as text. The value can be in one of the two formats like:

1. 2015-06-23 16:12:04.703
2. 8/6/2018 11:46:59 AM

When I cast the field to date and use it in a query, it works in a smaller set even if the formats are different. For example:

select A.SubscriberKey,A.DateIn
from ExtensionA A
where SubscriberKey in (100,200)
and cast(A.DateIn as Date) > '2/1/2020'

However when I use the same condition in a bigger query, the query fails with error:

Could not convert date and/or time from string data type.

select A.SubscriberKey,A.DateIn
from ExtensionA A
where cast(A.DateIn as Date) > '2/1/2020'

Any suggestions?

Answer 1

Turns out there is one test record that is not formatted properly which caused the exception.