We’re working with some user-submitted text that we need to convert into dates. Most of the data looks correct, but some of it looks glitchy:
expiration_date
8/16
5/17
damaged
6/16
See that line with “damaged” in there? That will cause to_date to throw an error:
select to_date('damaged', 'YY/MM');
ERROR: invalid value "da" for "YY"
DETAIL: Value must be an integer.
So I wrote this function:
create or replace function dt_or_null (s text, fmt text)
returns date
as
$$
begin
return to_date(s, fmt);
exception
when others then return null;
end;
$$ language plpgsql;
And this is how it works:
select 'damaged', dt_or_null('damaged', 'YY/MM');
+----------+------------+
| ?column? | dt_or_null |
+----------+------------+
| damaged | |
+----------+------------+
(1 row)