Convert the string to date

Question

I have a text field which holds date in below format

1/15/2020 11:43 AM
1/9/2020 11:43 AM

Now, what I need to do is, create a SOQL query that will filter this field. One approach can be, create a formula field that will convert the string to date. But in that case, what will the formula.

How can I do that?

N.B: I can not change the field type.

Answer 1

Doing this is inefficient--you will need to query the entire table to filter for records. This will result in slow reports for large objects and is not scalable. Create a secondary field (date-time) and convert the string to a date using a trigger or Process Builder, then you can filter on that efficiently.