Search code examples
sqloracleplsqldata-integrity

What would be the SQL Select statement for mixed dates in one column?

I have a customer who has legacy data stored in Oracle database. Such data contains mixed DATE values in one column in database (that field is VARCHAR(32)). For example they store '30-Sep-2009' and sometime '1254431689' (that is a timestamp in epoch time).

I have no option to split the data into multiple columns (so I have to deal with it).

Question is - How to convert the data on the fly in SQL Select statement?

Solution 
  • case when instr(thecol, '-')=0 then "convert one way"
     else "convert the other way"
end

    and the like, possibly with more when parts. Sad, really, and maybe worth encapsulating into a user-defined function to at least get it out of sight;-).