Re: Dates in inserts

>I want to use dd.mm.yy date format.
>I want to validate dates in inserts. How to do it ?

I was going to suggest that to force a format, try using
to_timestamp( '01.13.03', 'DD.MM.YY' ) but it does not
check the month field for validity.  Instead it returns
Jan 1 2004 (!).  I guess month 13 is January of the next year.

However, when you use any of the character Mon formats
for Mon, it does give an error message for a bad month
if you give it a bad month.


elein=# select to_timestamp( '13.01.03', 'DD.MM.YY' );
      to_timestamp
------------------------
 2003-01-13 00:00:00-08
(1 row)

elein=# select to_timestamp( '01.13.03', 'DD.MM.YY' );
      to_timestamp
------------------------
 2004-01-01 00:00:00-08
(1 row)

elein=# select to_date( '01 13 03', 'DD Mon YY' );
ERROR:  to_timestamp(): bad value for MON/Mon/mon

elein=# select to_date( '01-dEc-2003', 'DD-Mon-YYYY' );
  to_date
------------
 2003-12-01
(1 row)

elein=# select to_date( '01-dE-2003', 'DD-Mon-YYYY' );
ERROR:  to_timestamp(): bad value for MON/Mon/mon

elein@varlena.com


On Tuesday 01 April 2003 09:26, scott.marlowe wrote:
> On Tue, 1 Apr 2003, Tom Lane wrote:
> > "Nigel J. Andrews" <nandrews@investsystems.co.uk> writes:
> > > Probably becuase there is no 13th month so 01.13.03 can only be
> > > interpreted as the 13th of January.
> >
> > Right, and the same goes for 13.01.03: even if your datestyle is mmddyy,
> > the date parser will take this as ddmmyy, because otherwise it couldn't
> > be valid.  AFAIK there is no way to force the date parser to reject the
> > input instead.  Datestyle is used to drive the interpretation when the
> > input is ambiguous, but not when there is only one interpretation that
> > will work.
> >
> > If you prefer to be stiff-necked then I'd recommend putting some
> > validation on the client side.
>
> I think the better answer is to only insert dates in an unambiguous
> format.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

--
----------------------------------------------------------------------------------------
elein@varlena.com     Database Consulting     www.varlena.com
              I have always depended on the [QA] of strangers.