Received: from sss.pgh.pa.us ([192.204.191.242]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fBVG3Cm72413 for ; Mon, 31 Dec 2001 11:03:13 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id fBVG2lg12929; Mon, 31 Dec 2001 11:02:47 -0500 (EST) To: "Aasmund Midttun Godal" cc: pgsql-sql@postgresql.org, Karel Zak Subject: Re: Unpredictable text -> date implicit casting behaviour/to_date bug In-reply-to: <20011231143815.8427.qmail@213-145-170-138.dd.nextgentel.com> References: <20011231143815.8427.qmail@213-145-170-138.dd.nextgentel.com> Comments: In-reply-to "Aasmund Midttun Godal" message dated "Mon, 31 Dec 2001 14:38:15 +0000" Date: Mon, 31 Dec 2001 11:02:47 -0500 Message-ID: <12926.1009814567@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200112/269 X-Sequence-Number: 6091 "Aasmund Midttun Godal" writes: > The implicit casting of text to date is normally quite good, however i > think it should only use one method during a given query (or maybe > transaction?) Try setting DateStyle to match the date style you are using. Postgres is doing the best it can with ambiguous input. > Added to the fact that to_date has a bug: > SELECT ordredato, ordredato::date, to_date(ordredato, 'DD.MM.YY') from old_faktura where status = 'ANN'; > ordredato | ?column? | to_date > -----------+------------+--------------- > 18.04.00 | 2000-04-18 | 0001-04-18 BC > 30.08.01 | 2001-08-30 | 2001-08-30 > 18.04.00 | 2000-04-18 | 0001-04-18 BC I agree, this is not desired behavior. The problem seems to be that the logic in to_timestamp() tries to use "field is not zero" as a substitute test for "field was provided". At least in the case of the year fields, this is *not* workable. There must be a separate flag bit. regards, tom lane