X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 5B13FD1D5A8 for ; Tue, 2 Dec 2003 09:57:51 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 84767-08 for ; Tue, 2 Dec 2003 05:57:22 -0400 (AST) Received: from ara.zf.jcu.cz (ara.zf.jcu.cz [160.217.161.4]) by svr1.postgresql.org (Postfix) with ESMTP id 2F82DD1D61F for ; Tue, 2 Dec 2003 05:57:19 -0400 (AST) Received: by ara.zf.jcu.cz (Postfix, from userid 5770) id 201A99863; Tue, 2 Dec 2003 10:57:14 +0100 (CET) Date: Tue, 2 Dec 2003 10:57:14 +0100 From: Karel Zak To: Christoph Haller Cc: "Alexander M. Pravking" , pgsql-sql@postgresql.org Subject: Re: Validity check in to_date? Message-ID: <20031202095713.GB17884@zf.jcu.cz> References: <20031127141125.GC87728@dyatel.antar.bryansk.ru> <200312020927.KAA17321@rodos> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <200312020927.KAA17321@rodos> User-Agent: Mutt/1.3.28i X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200312/12 X-Sequence-Number: 16178 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 http://home.zf.jcu.cz/~zakkr/