Postgres SQL - select inconsistent date formats from character field

Question

I'm stuck on a date formatting issue and hoping someone can help me out!

Our DB has a date field which was formatted as a character field by our programming team because of different import sources/formats.

I am ideally aiming to create a view which ends up with date only which I will then use in the reporting tool (dbxtra) for daily/monthly grouping on pivots and reports.

Example data:

"2015-05-30"

"3/06/2015 12:00 AM"

I have tried:

• converting to date with ::date

• substr to take first 10 characters

Any suggestions?

Answer 1

I would write a stored procedure for this which you can call in your sql query. Something like this (pseudo code):

CREATE OR REPLACE FUNCTION convertMessedUpStringsToDate(i_val1 varchar) RETURNS timestamp
declare 
  retval timestamp;
  formatArray varchar[];
begin
  -- build array with possible formats, most used in front
  formatArray[0] = 'YYYY-MM-DD';
  formatArray[1] = 'DD/MM/YYYY HH12:MI AM';

  for i in 0 .. formatArray.count loop
    begin
      retval = to_timestamp(i_val, formatArray[i]);
    exception
      -- catch any exception and try the next format
    end;
  end loop;

  return retval;
end;

Another option is to parse your input using a regex and find the matching pattern that way. Anyway I hope you don't have too many different patterns.