On one data extension, I have a DateIn field modeled as text. The value can be in one of the two formats like:
- 2015-06-23 16:12:04.703
- 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'