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?


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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.