Re: Finding bogus dates

Scott Ribe wrote:

> Actually, that's the core of the direct query!
>
> select * from foo where to_date(olddate,'YYYY-MM-DD')::text <>
> olddate;
>
> The format of the exported dates matches the default date::text format
> because I specified the export that way. If not, the query would be
> a little
> more complicated by having to specify the format, but the same
> basic idea
> still would work.

But this won't work if one had a text column of dates in various
formats, right?  You could use a disjunction with lots of formats in
your query - or better yet, put all the formats you can think of in a
temp table and join against it:

   select * from foo
     where not exists (select format from dateformats
    where to_char(to_date(olddate, format), format) = olddate);

But the date casting code seems to be even more general, or at least
seems to know about many more formats than I'd be likely to dream up
on my own.

- John D. Burger
   MITRE