From: | elein <elein(at)sbcglobal(dot)net> |
---|---|
To: | "scott(dot)marlowe" <scott(dot)marlowe(at)ihs(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | "Nigel J(dot) Andrews" <nandrews(at)investsystems(dot)co(dot)uk>, Michal Kalanski <kalanskim(at)zetokatowice(dot)pl>, <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Dates in inserts |
Date: | 2003-04-02 00:13:25 |
Message-ID: | 200304020016.h320GKqC347206@pimout2-ext.prodigy.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
>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.
From | Date | Subject | |
---|---|---|---|
Next Message | Martijn van Oosterhout | 2003-04-02 00:19:49 | Re: Postgres logs |
Previous Message | Wayne Armstrong | 2003-04-01 22:34:23 | Re: COBOL Micro Focus Application accessing Postgresql |