Oracle Date Comparison in Character Column
I have a query I am trying to write against a custom table within PeopleSoft. This custom table has some character columns that are free form, but in this case storing dates, so the user can type something like "03/14/2017" for March 14, 2017. The query needs to evaluate these entries as date criteria, determining whether the date in the column is less than the current date. This is a little challenging because the field could be populate or it could be empty, although in PeopleSoft empty actually means a single space character. We are also assuming that if it is populated it is always in the format MM/DD/YYYY. A couple different approaches are below, but obviously there are huge issues with this type of implementation... what happens is someone enters something that doesn't even look like a date? Probably an error. At least in my case the data entry is somewhat controlled. In both examples I do want to return rows where the date column is blank or th...