From: Karel Zak <zakkr@zf.jcu.cz>
To: Christoph Haller <ch@rodos.fzk.de>
Cc: Alexander M. Pravking <fduch@antar.bryansk.ru>
Cc: pgsql-sql@postgresql.org
Subject: Re: Validity check in to_date?
Date: Tue, 2 Dec 2003 10:57:14 +0100
Message-ID: <20031202095713.GB17884@zf.jcu.cz> (raw)
In-Reply-To: <200312020927.KAA17321@rodos>
References: <20031127141125.GC87728@dyatel.antar.bryansk.ru>
<200312020927.KAA17321@rodos>
On Tue, Dec 02, 2003 at 10:27:04AM +0100, Christoph Haller wrote:
> >
> > I just discovered that to_date() function does not check if supplied
> > date is correct, giving surprising (at least for me) results:
> >
> > fduch=# SELECT to_date('31.11.2003', 'DD.MM.YYYY');
> > to_date
> > ------------
> > 2003-12-01
> >
> > or even
> >
> > fduch=# SELECT to_date('123.45.2003', 'DD.MM.YYYY');
> > to_date
> > ------------
> > 2007-01-03
> >
> > to_timestamp() seems to work the same way. It's probably useful sometimes,
> > but not in my case... Is it how it supposed to work?
> > If so, how can I do such a validity check?
> > If not, has something changed in 7.4?
No change in 7.4. Maybe in 7.5 or in some 7.4.x.
> As far as I know these results are correct in terms of the underlying
> C-library function mktime(). This function is intended to be used when
> adding/subtracting intervals from a given timestamp.
> I don't know of any postgres function doing the check you're looking for.
> But I can't believe this is the first time this topic is brought up.
> You may search the archives on "date plausibility" are related terms.
The others PostgreSQL stuff which full parse (means check ranges)
date/time is less optimistic with this:
# select '31.11.2003'::date;
ERROR: date/time field value out of range: "31.11.2003"
Karel
--
Karel Zak <zakkr@zf.jcu.cz>
http://home.zf.jcu.cz/~zakkr/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: zakkr@zf.jcu.cz, ch@rodos.fzk.de, fduch@antar.bryansk.ru
Subject: Re: Validity check in to_date?
In-Reply-To: <20031202095713.GB17884@zf.jcu.cz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox