Re: this date format thing.

On Thu, 3 Apr 2003 16:17:05 +0200, Karel Zak wrote:
> On Thu, Apr 03, 2003 at 12:36:07PM +0100, Peter Haworth wrote:
> >
> > jnlstats=> set datestyle='ISO';
> > SET
> >
> > jnlstats=> select '2001-31-12'::date;
> > date
> > ------------
> > 2001-12-31
> > (1 row)
> >
> > jnlstats=> select to_date('2001-31-12','yyyy-mm-dd');
> > to_date
> > ------------
> > 2003-07-14
> > (1 row)
>
> Are you sure is there 31 months ('mm')?

No. That's the point. Those examples are of dates being accepted which
shouldn't be. If someone enters a date with the month and day swapped,
postgres doesn't necessarily reject it.

When casting text to dates, if it can be made to validate by swapping
day and month, that's what postgres does. This makes both yyyy-mm-dd
and yyyy-dd-mm formats valid for input, which means that the application
can't trust that the user is entering the date they think they are.

When using to_date(), there seems to be no range checking at all. This
is even worse than the above, which will at least reject strings if they
don't fit into any date format. to_date() seems to treat the month as
"number of months since the beginning of the specified year" rather than
"calendar month within the specified year".

--
Peter Haworth pmh(at)edison(dot)ioppublishing(dot)com
End users will report anything as a bug:
"Your web site sucks because it didn't work after I put jelly in my keyboard."
-- Mark Jason Dominus