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

Frank Miles <fpm(at)u(dot)washington(dot)edu> writes:
> 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.

That pretty much assumes that you've already validated the input and
converted it to an unambiguous form.

I think much of this discussion is missing the point. ISTM when you're
dealing with programmatic output, it's fairly easy to ensure that you
are on the same page as the other program, and in that case there's a
good argument for being strict about the expected field order. But
when you are dealing with hand-entered input, you *do not know* what
the user meant by input such as '01/03/2003'. You may think you know,
but you're just fooling yourself. The only really bulletproof way of
handling the matter is to close the loop by repeating the data back to
the user in an obviously unambiguous format, say 03-Jan-2003 or
01-Mar-2003. If that wasn't what he meant, he can change it. When you
handle things that way, there's a very good case for being as permissive
as possible in the parsing of the initial input.

PG's existing date parsing code is intended to support the second
scenario. I don't mind offering an option to make it support the first
scenario better --- but I will resist ripping out support for the second.

regards, tom lane