Re: A creepy story about dates. How to prevent it?

On Thu, 19 Jun 2003, Frank Miles wrote:

> On Thu, 19 Jun 2003, Bruno Wolff III wrote:
>
> > On Thu, Jun 19, 2003 at 02:43:12 -0500,
> > Ron Johnson <ron(dot)l(dot)johnson(at)cox(dot)net> wrote:
> > >
> > > OTOH, Andrew Snow's method (alway use ANSI standard YYYY-MM-DD)
> > > is guaranteed to work. Have your app convert to that format before
> > > inserting, and then PostgreSQL is guaranteed to puke if there's
> > > a problem.
> >
> > No it isn't. In 7.4:
> > area=> select '2003-20-02'::date;
> > date
> > ------------
> > 2003-02-20
> > (1 row)
>
> If the application always passes the date to Postgres with the three-letter
> month name where appropriate, and use the 4-digit year, it should be
> comparatively bulletproof. At least, bulletproof in its interpretation --
> the application can always garble things.

I would say that whether the old 02/14/2003 -> 14/02/2003 conversion stuff
stays in, the 2003-14-02 -> 2003-02-14 stuff should NOT.

And the fact that "other databases" do it that way is not an argument.
Postgresql has always had a higher standard re: data integrity than most
databases. I can't imagine anyone actually preferring the silent
conversion over the error, since it's a hit or miss thing and can result
in bad data silently.