Re: this date format thing.

On Thu, Apr 03, 2003 at 03:40:59PM +0100, Peter Haworth wrote:
> 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 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".

 Hmm, You're probably right that to_date/timestamp is too optimistic
 and liberal if you want to use it for data checking. Oracle:

 SVRMGR> select to_date('2001-31-12','yyyy-mm-dd') from dual;
 TO_DATE('
 ---------
 ORA-01843: not a valid month


 I will fix it in to_date/timestamp in 7.4. Thanks.

    Karel


--
 Karel Zak  <zakkr@zf.jcu.cz>
 http://home.zf.jcu.cz/~zakkr/