Postgresql: convert a string to a date or NULL

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)

Published by

matt

My name is Matt Wilson. I make websites for Kiwee to pay the bills. I live in Cleveland Heights, Ohio, with my wife and son.