Re: Finding bogus dates

Add a date field to the table.

Run UPDATE "foo" SET "newDate" = to_date("oldDate","MM/DD/YYYY").

Bad dates like 02/31/2006 will be converted to sane dates. 02/31/2006
--> 03/03/2006.

Now run SELECT * FROM "foo" WHERE to_char("newDate","MM/DD/YYYY") <>
"oldDate". If a date got changed for sanity reasons, it'll be
different.

That should get most of 'em.

--
Brandon Aiken
CS/IT Systems Engineer
-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Scott Ribe
Sent: Thursday, January 18, 2007 1:26 PM
To: pgsql-general postgresql.org
Subject: [GENERAL] Finding bogus dates

Suppose that I have a varchar column which contains dates, and some of
them
are bogus, such as '1968-02-31'. Is it possible to specify a query
condition
"cannot be cast to a valid date". (It's legacy data, and I'm trying to
clean
it up before importing into the final schema.) There probably aren't
that
many, and I can just keep hitting pg errors one row at a time until I
work
through this, but...

--
Scott Ribe
scott_ribe(at)killerbytes(dot)com
http://www.killerbytes.com/
(303) 722-0567 voice

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
message can get through to the mailing list cleanly