Thread: More PostgreSQL conversion fun

Hi all,

I have a varchar field in a table which contains dates in yyyy-mm-dd format.
The problem is that some have entered invalid dates like 1975-01-00 and I
want to convert it to a date field to avoid this nonsense. Is there a way to
test for failure of a type conversion and insert a NULL on failure?

Thanks,
Nick

Nick Wiltshire wrote:
> Hi all,
>
> I have a varchar field in a table which contains dates in yyyy-mm-dd
> format. The problem is that some have entered invalid dates like
> 1975-01-00 and I want to convert it to a date field to avoid this
> nonsense. Is there a way to test for failure of a type conversion and
> insert a NULL on failure?
>

Not the question you asked, but perhaps a suitable alternative: before
attempting to convert, scan all the rows in the table looking for any
contents of that column not fitting the format yyyy-mm-dd.  Then you can
either fix them or null them out.

--
Guy Rouillier

> I have a varchar field in a table which contains dates in yyyy-mm-dd format.
> The problem is that some have entered invalid dates like 1975-01-00 and I
> want to convert it to a date field to avoid this nonsense. Is there a way to
> test for failure of a type conversion and insert a NULL on failure?

Hi,
If you are using 8.0 or above you could write a plpgsql function and use
a for select loop with a exception handler.  In the loop try casting the
column in question to date type and if a error is raised because of a
invalid date set the column value to null.

See this section in the docs:
http://www.postgresql.org/docs/8.1/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING

After you have cleaned up the bad dates, then change the column to a
date type.

Note: I have not tested this, but I think it should work :-)

Later,


--
Tony Caduto
AM Software Design
Home of PG Lightning Admin for Postgresql
http://www.amsoftwaredesign.com