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(at)varlena(dot)com

On Tuesday 01 April 2003 09:26, scott.marlowe wrote:
> On Tue, 1 Apr 2003, Tom Lane wrote:
> > "Nigel J. Andrews" <nandrews(at)investsystems(dot)co(dot)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(at)varlena(dot)com Database Consulting www.varlena.com
I have always depended on the [QA] of strangers.